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

innovator gravatar image

innovator
335 15 15 17

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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x344
x290
x22
x6

asked: Mar 06, 2012 at 09:59 AM

Seen: 990 times

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