x

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

more ▼

asked Mar 06, 2012 at 09:59 AM in Default

avatar image

innovator
335 15 15 20

Can't it be done thourgh sql statement?

Mar 06, 2012 at 10:19 AM innovator

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.

Mar 06, 2012 at 10:45 AM Usman Butt

Please guide me with tools that we can use with SSMS 2005 since I have that one!

Mar 06, 2012 at 10:54 AM innovator
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 06, 2012 at 10:08 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x409
x391
x33
x9

asked: Mar 06, 2012 at 09:59 AM

Seen: 1202 times

Last Updated: Mar 06, 2012 at 10:54 AM

Copyright 2016 Redgate Software. Privacy Policy