SQL transform table structure

Question

I'm creating DTS - packet in SSIS:

table users_properties:

[id] [user_id] [prop_name] [prop_value]
 1    1         LastName    Hendrix
 2    1         FirstName   John
 3    2         LastName    Adamson
 4    2         FirstName   Smith
 5    2         Age         28

How can I get the table with this structure:

[user_id] [LastName] [FirstName] [Age]
1          Hendrix     John

Is it possible to do this without JOIN (perfomance !!!), for example, by case-statements or by components in Visual Studio? Please advise how to do this.

Not sure why you think that JOINs will cause a performance issue. With appropriate indexes this will probably be as efficient or more so than pivoting. — Martin Smith

MatBailie MatBailie · Accepted Answer · 2011-12-17T14:16:55

Using CASE statements...

SELECT
  user_id,
  MAX(CASE WHEN prop_name = 'FirstName' THEN prop_value END) AS FirstName,
  MAX(CASE WHEN prop_name = 'LastName'  THEN prop_value END) AS LastName,
  MAX(CASE WHEN prop_name = 'Age'       THEN prop_value END) AS Age
FROM
  yourTable
GROUP BY
  user_id

Note: This assumes that no user_id has more the one value for any prop_name, and does not do any casting to different datatypes, but you can add that in if necessary.

Alternatively, you could lookup how to PIVOT in SQL. Though I know many people are put-off by this and prefer to use the above CASE/Aggregate method. In either version you must know in advance what columns you want as a result, it can't be done to a dynamic number of columns without dynamic SQL.

EDIT Pivot example due to use of deprecated NTEXT type.

SELECT
  pvt.user_id, [LastName], [FirstName], [Age]
FROM 
  yourTable
PIVOT
  ( prop_value FOR prop_name IN ( [LastName], [FirstName], [Age] ) ) AS pvt
ORDER BY
  pvt.user_id;

SQL transform table structure

1 Answers