SQL Server 2008 Vertical data to Horizontal

Question

I apologize for submitting another question on this topic, but I've read through many of the answers on this and I can't seem to get it to work for me.

I have three tables I need to join and pull info on. One of the tables is only 3 columns and stores the data vertically. I would like to transpose that data to a horizontal format.

The data will look like this if I just join and pull:

SELECT 
   a.app_id, 
   b.field_id, 
   c.field_name,
   b.field_value 
FROM table1 a
JOIN table2 b ON a.app_id = b.app_id
JOIN table3 c ON b.field_id = c.field_id  --(table3 is a lookup table for 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, I would like it to look like 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 the past, I just resorted to looking up all the field_id's I needed in my data and created CASE statements for each one. The app the users are using contains data for multiple products, and each product contains different fields. Considering the number of products supported and the number of fields for each product (many, many more than the basic example I showed, above) it takes a long time to look them up and write out huge chunks of CASE statements.

I was wondering if there's some cheat-code out there to achieve what I need without having to look up the field_ids and writing things out. I know the PIVOT function is likely what I'm looking for, however, I can't seem to get it to work correctly.

Think you guys could help out?

Bellyband

Answer 1

You can use the PIVOT function to convert your rows of data into columns.

Your original query can be used to retrieve all the data, the only change I would make to it would be to exclude the column b.field_id because this will alter the final display of the result.

If you have a known list of field_name values that you want to turn into columns, then you can hard-code your query:

select app_id,
  [First Name], [Last Name], [DOB],
  [Mailing Addr], [Zip]
from
(
  SELECT 
     a.app_id, 
     c.field_name,
     b.field_value 
  FROM table1 a
  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)
  for field_name in ([First Name], [Last Name], [DOB],
                     [Mailing Addr], [Zip])
) piv;

See SQL Fiddle with Demo.

But if you are going to have an unknown number of values for field_name, then you will need to implement dynamic SQL to get the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Field_name) 
                    from Table3
                    group by field_name, Field_id
                    order by Field_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT app_id,' + @cols + ' 
            from 
            (
              SELECT 
                 a.app_id, 
                 c.field_name,
                 b.field_value 
              FROM table1 a
              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)
                for field_name in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. Both of these this will give a 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 |

Sauropod

This is exactly what I needed. I don't have the rep to upvote you, but if anyone else out here sees this, please upvote for me! – Bellyband

Answer 2

Try this

SELECT 
    [app_id]
    ,MAX([First Name]) AS [First Name]
    ,MAX([Last Name]) AS [Last Name]
    ,MAX([DOB]) AS [DOB]
    ,MAX([Mailing Addr]) AS [Mailing Addr]
    ,MAX([Zip]) AS [Zip] 
FROM Table1
PIVOT
(
    MAX([field_value]) FOR [field_name] IN ([First Name],[Last Name],[DOB],[Mailing Addr],[Zip])
) T
GROUP BY [app_id]

SQL FIDDLE DEMO

Jeweller

Answer 3

bluefeet's answer was the right one for me, but I needed distinct on the column list:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT Distinct ',' + QUOTENAME(Field_name) 
                from Table3
                group by field_name, Field_id
                order by ',' + QUOTENAME(Field_name) 
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT app_id,' + @cols + ' 
        from 
        (
          SELECT 
             a.app_id, 
             c.field_name,
             b.field_value 
          FROM table1 a
          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)
            for field_name in (' + @cols + ')
        ) p '

execute sp_executesql @query;

Demean

Answer 4

This would solve using group by and MAX function, instead of pivot:

 SELECT PK_ID, MAX(PHONE) AS PHONE, MAX(MAIL) AS MAIL

 FROM ( 
    
    SELECT 
                        PK_ID,
                        CASE  
                          WHEN CONTACT_ALIAS.CONTACT_TYPE = 'COMPANY'  THEN   CONTACT_ALIAS.CONTACT_VALUE
                        END AS PHONE ,
                        
                        CASE  
                          WHEN CONTACT_ALIAS.CONTACT_TYPE = 'BUSINESS'  THEN   CONTACT_ALIAS.CONTACT_VALUE
                        END AS MAIL 
                
                    FROM  T_CONTACT_EMPLOYERS  CONTACT_ALIAS
                    WHERE CONTACT_ALIAS.CONTACT_TYPE IN ('COMPANY' , 'BUSINESS') 
     ) TEMP
                 GROUP BY PK_ID

Ferrigno

Answer 5

USe of SQL Pivot

   SELECT [Id], [FirstName], [LastName], [Email] 
FROM
(
 SELECT Id, Att_Id, Att_Value FROM VerticalTable
) as source
PIVOT
(
 MAX(Att_Value) FOR Att_Id IN ([FirstName], [LastName], [Email])
) as target

Precedence

Can you add a short description? – Brokaw