question

innovator avatar image
innovator asked

Cross tab data required

Please help with said case,Thnx in advance create table ClientMaster ( Description varchar(50), MF int, FD int, AUM int, ClientID varchar(50)) Data is as follows 'Dep1',1,2,4,'CL101' 'Dep2',8,2,4,'CL101' 'Dep1',1,5,4,'CL102' 'Dep2',1,7,23,'CL102' 'Dep1',1,3,4,'CL103' 'Dep2',1,76,4,'CL103' 'Dep1',1,2,6,'CL104' 'Dep2',1,2,4,'CL104' Output format should be as below depeding on Desc Dep1 Dep2 ClientCode MF FD AUM MF FD AUM CL101 1 2 4 8 2 4 CL102 1 5 4 1 7 23
sql-servertsqljoincross-tab
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It can be to some extent. For e.g. SELECT [ClientID] , SUM( CASE WHEN [Department] = 'Dep1' THEN [MF] ELSE 0 END) [MF] , SUM(CASE WHEN [Department] = 'Dep1' THEN [FD] ELSE 0 END) [FD] , SUM(CASE WHEN [Department] = 'Dep1' THEN [AUM] ELSE 0 END)[AUM] , SUM( CASE WHEN [Department] = 'Dep2' THEN [MF] ELSE 0 END) [MF] , SUM(CASE WHEN [Department] = 'Dep2' THEN [FD] ELSE 0 END) [FD] , SUM(CASE WHEN [Department] = 'Dep2' THEN [AUM] ELSE 0 END)[AUM] FROM [ClientMaster] GROUP BY [ClientID] But t-sql is not meant to be used for presentation. As @Fatherjack already told you, for that you have to use tools, which will make it pretty simple. Otherwise, you could find yourself in a predicament.
1 Like 1 ·
Can't it be done thourgh sql statement?
0 Likes 0 ·
Please guide me with tools that we can use with SSMS 2005 since I have that one!
0 Likes 0 ·

1 Answer

· Write an Answer
Fatherjack avatar image
Fatherjack answered
Personally I leave all of this sort of formatting to be done in the presentation layer, use the database engine collect your data and then use SSRS or PowerPivot or other BI tool to do its work and make it all look pretty.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.