Reputation: 11
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.
Upvotes: 1
Views: 199
Reputation: 86808
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;
Upvotes: 2