1

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.

T.Rob
  • 31,522
  • 9
  • 59
  • 103
user1095307
  • 11
  • 2
  • 3
    Not sure why you think that `JOIN`s will cause a performance issue. [With appropriate indexes this will probably be as efficient or more so than pivoting.](http://stackoverflow.com/questions/7448453/sql-server-pivot-vs-multiple-join/7449213#7449213) – Martin Smith Dec 17 '11 at 14:43

1 Answers1

2

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;
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thanks for your answer, but trere is one problem: http://s017.radikal.ru/i431/1112/2a/a64fdf2e7a68.jpg The MAX function works with numeric data types, otherwise, I'm using "ntext" type for the field "prop_value" – user1095307 Dec 17 '11 at 14:29
  • @user1095307 - You shouldn't be using NTEXT anymore. It has been replaced with NVARCHAR(MAX) instead ( http://msdn.microsoft.com/en-us/library/ms187993.aspx ). Which *is* allowed in the MAX() function ( http://msdn.microsoft.com/en-us/library/ms187751.aspx ). If you have no choice, lookup Pivot, and I'll add an example shortly ( http://msdn.microsoft.com/en-us/library/ms177410.aspx ). – MatBailie Dec 17 '11 at 14:35
  • Ok, thanks a lot for your answer and links! It's all good with your query, it was my mistake( – user1095307 Dec 17 '11 at 14:52
  • 3
    @user1095307 - In my opinion, the best way to learn is to make an effort, make mistakes and learn from them. As long as you learn from them, mistakes are very informative :) – MatBailie Dec 17 '11 at 14:54