pivot - SQL Server 2008 Vertical data to Horizontal -

i apologize submitting question on topic, i've read through many of answers on , can't seem work me.

i have 3 tables need join , pull info on. 1 of tables 3 columns , stores data vertically. transpose data horizontal format.

the data if join , pull:

select     a.app_id,     b.field_id,     c.field_name,    b.field_value  table1 join table2 b on a.app_id = b.app_id join table3 c on b.field_id = c.field_id  --(table3 lookup table field names) 

result:

app_id  |  field_id  |   field_name   |  field_value -----------------------------------------------------  1234   |    101     |    first name  |     joe    1234   |    102     |     last name  |     smith  1234   |    105     |       dob      |   10/15/72  1234   |    107     |  mailing addr  |   po box 1234  1234   |    110     |      zip       |     12345        1239   |    101     |    first name  |     bob    1239   |    102     |     last name  |     johnson  1239   |    105     |       dob      |   12/01/78  1239   |    107     |  mailing addr  |   1234 n star ave  1239   |    110     |      zip       |     12456   

instead, this:

app_id  |  first name  |   last name   |    dob    |   mailing addr   |  zip --------------------------------------------------------------------------  1234   |    joe       |     smith     |  10/15/72 |   po box 1234    | 12345       1239   |    bob       |    johnson    |  12/01/78 |  1234 n star ave | 12456  

in past, resorted looking field_id's needed in data , created case statements each one. app users using contains data multiple products, , each product contains different fields. considering number of products supported , number of fields each product (many, many more basic example showed, above) takes long time them , write out huge chunks of case statements.

i wondering if there's cheat-code out there achieve need without having field_ids , writing things out. know pivot function i'm looking for, however, can't seem work correctly.

think guys out?

you can use pivot function convert rows of data columns.

your original query can used retrieve data, change make exclude column b.field_id because alter final display of result.

if have known list of field_name values want turn columns, can hard-code query:

select app_id,   [first name], [last name], [dob],   [mailing addr], [zip] (   select       a.app_id,       c.field_name,      b.field_value    table1   inner join table2 b      on a.app_id = b.app_id   inner join table3 c      on b.field_id = c.field_id  ) d pivot (   max(field_value)   field_name in ([first name], [last name], [dob],                      [mailing addr], [zip]) ) piv; 

see sql fiddle demo.

but if going have unknown number of values field_name, need implement dynamic sql result:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(field_name)                      table3                     group field_name, field_id                     order field_id             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select app_id,' + @cols + '                           (               select                   a.app_id,                   c.field_name,                  b.field_value                table1               inner join table2 b                  on a.app_id = b.app_id               inner join table3 c                  on b.field_id = c.field_id              ) x             pivot              (                 max(field_value)                 field_name in (' + @cols + ')             ) p '  execute sp_executesql @query; 

see sql fiddle demo. both of these give result:

| app_id | first name | last name |      dob |    mailing addr |   zip | ------------------------------------------------------------------------ |   1234 |        joe |     smith | 10/15/72 |     po box 1234 | 12345 | |   1239 |        bob |   johnson | 12/01/78 | 1234 n star ave | 12456 |