Query to convert row values into column names

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

SQL Server 2000 Forums

Transact-SQL (2000)

Query to convert row values into column names

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-09 : 08:13:37

Shailendra writes "Hi There,
This is really a problem in Sqlserver which i am not able to solve it...What i need to do is :

Master Table

WorkSheetMast

WorkID
PoNo
PurType
.
.
.
10 columns

Detail Table

WorkSheetDet

WorkID
UserId
Desription

This is table structure ....The output is

WorkId PoNo PurType . . . . . .User1 User2 User3....on
val 1 val2 val3 ..............Descption1 Descption2 Descption3.

Actually i need to convert userid row values into column names where A.WorkID = B.WorkId

Pls help me as the requirement is very urgent

Regards
Shailendra Sethi"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-09 : 08:16:43

You need to cross-tabulate (cross-tab) your data, the CASE expression will work for you:

SELECT WM.WorkID, WM.PONo, WM.PurType,
Max(CASE WHEN WS.UserID=1 THEN WS.Description END) AS User1,
Max(CASE WHEN WS.UserID=2 THEN WS.Description END) AS User2,
Max(CASE WHEN WS.UserID=3 THEN WS.Description END) AS User3
FROM WorkSheetMast WM INNER JOIN
WorkSheetDet WS ON (WM.WorkID=WS.WorkID)
GROUP BY WM.WorkID, WM.PONo, WM.PurType

If you have a dynamic number of user ID's, you can use this to generate the cross-tab automatically:

http://www.sqlteam.com/item.asp?ItemID=2955

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources