SQL query with transpose - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query with transpose

Author

Topic

Trinity99
Starting Member

2 Posts

Posted - 2014-11-03 : 12:32:51

ALL

I need to create a report out of our database.
Simplified example:

Table 1:

LINK_ID Item
123456 Item1
123457 Item2
123458 Item3
123459 Item4

Table 2

Link_ID PROP_ID Property
123456 1 Property_1
123456 2 Property_2
123456 3 Property_3
123456 4 Property_4
123457 1 Property_1
123457 2 Property_2
123457 3 Property_3
123457 4 Property_4
123458 1 Property_1
123458 2 Property_2
123458 3 Property_3
123458 4 Property_4
123459 1 Property_1
123459 2 Property_2
123459 3 Property_3
123459 4 Property_4

When I Join this 2 tables i get:

LINK_ID Item PROP_ID Property
123456 Item1 1 Property_1
123456 Item1 2 Property_2
123456 Item1 3 Property_3
123456 Item1 4 Property_4
123457 Item2 1 Property_1
123457 Item2 2 Property_2
123457 Item2 3 Property_3
123457 Item2 4 Property_4
123458 Item3 1 Property_1
123458 Item3 2 Property_2
123458 Item3 3 Property_3
123458 Item3 4 Property_4
123459 Item4 1 Property_1
123459 Item4 2 Property_2
123459 Item4 3 Property_3
123459 Item4 4 Property_4

But I need:

LINK_ID Item PROP_ID1 PROP_ID2 PROP_ID3 PROP_ID4
123456 Item1 Property_1 Property_2 Property_3 Property_4
123457 Item2 Property_1 Property_2 Property_3 Property_4
123458 Item3 Property_1 Property_2 Property_3 Property_4
123459 Item4 Property_1 Property_2 Property_3 Property_4

Thanks in advance for your help !!

Ivo

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 12:41:22

Add a pivot clause:


PIVOT Max(Property) for Prop_ID in ([1],[2],[3],[4]))p

Trinity99
Starting Member

2 Posts

Posted - 2014-11-03 : 12:58:44

Thank you very much but I got another answer in another forum which works for me:

SELECT a.LINK_ID,
Max(CASE
WHEN Prop_ID = 1 THEN Property
END) AS PROP_ID1,
Max(CASE
WHEN Prop_ID = 2 THEN Property
END) AS PROP_ID2,
Max(CASE
WHEN Prop_ID = 3 THEN Property
END) AS PROP_ID3,
Max(CASE
WHEN Prop_ID = 4 THEN Property
END) AS PROP_ID4
FROM table1 a
INNER JOIN table2 b
ON a.LINK_ID = b.LINK_ID
GROUP BY a.LINK_ID

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 13:10:35

yes, that's another way of doing the same thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources