Need a SQL query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need a SQL query

Author

Topic

great_mamun
Starting Member

14 Posts

Posted - 2011-04-12 : 07:30:44

Dear All,
I have the following table

id type week
1002 B w1
1002 B w3
1001 B w4
1003 B w1
1003 B w2
1003 B w3
1003 B w4

i want to produce following (col is fixed week-1 to week-4)

id type week-1 week-2 week-3 week-4
1001 B NA NA NA w4
1002 B w1 NA w3 NA
1003 B w1 w2 w3 w4

1001 has only w4 though 1002 and 1003 have w1,w3 and w1,w2,w3,w4 respectively.

Best Regards,
Abdullah Al Mamun

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-12 : 07:47:49

You can do this using PIVOT, like this:

SELECT
	ID,
	[Type],
	ISNULL(W1, 'NA') AS [Week-1],
	ISNULL(W2, 'NA') AS [Week-2],
	ISNULL(W3, 'NA') AS [Week-3],
	ISNULL(W4, 'NA') AS [Week-4]
FROM
	YourTable
	PIVOT(MAX([week]) FOR [week] IN ([w1], [w2], [w3], [w4])) P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources