Previewing data in cross tab format

Question

Previewing data in cross tab format

andy behrendt

SSC Enthusiast

Points: 177
More actions
April 19, 2010 at 2:37 pm

#231870

Hi All,
I am looking for a solution to a problem I have presenting data in what I guess is a cross tab style, I have a table at the moment that has the following the columns:
CreatedBy - Text field containing username
CreatedDate - SmallDateTime field with date the call was created
ServiceName - Text field containing the category for the call
CallID - Integer field containing unique ID of call
Medium - Text field containing details of the how the call was raised (e.g. phone or email)
AssignedTo - Text field containing the group the call was assigned to
CallerOrganisation - Text field containing code identifying which group within the organisation that the caller belongs to
I need to present the information in two similar ways but I am unsure of how to do this. The first view is to display the columns as "Medium, 2007, 2008, 2009, 2010", where the data in columns 2007-2010 is a count of all the calls in that year grouped by Medium. The second way to view the data will be to display "Medium, Year, Jan, Feb, Mar etc" where again the months will be a count of all the calls in that month grouped by year and medium.
I know I could easily do this in Excel by exporting data and manipulating it, but it may become an ongoing thing and I do not want to manually do this everytime. I would rather write it once and then run it out from a web page with a single click. Is there a predefined method in sql for performing this sort of analysis or can someone please point me in the right direction for achieving this with custom code?
Cheers
ab

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the CrossTab article referenced in my signature. Once you know how that works you probably should go to the "next level", DynamicCrossTab, referenced in my signature, too.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

Oddly enough, I agree. 😉 A dynamic Cross Tab would be the way to go and the two articles Lutz cited will teach you how to do that and more.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andy behrendt SSC Enthusiast Points: 177 More actions · Answer 3

Thanks for the pointers to the crosstab articles. I have had a look and had a rough go at it, but I found the result was a bit strange. I only returned one value for each grouping of "Medium" and this was in only one column of the crosstab. For example:

Chat 00740

Face to Face 00012

Phone 01784000

Mail 04000

Voicemail 129000

There should have been a value for pretty much each cell in the crosstab, but I suspect that it only returns the count of the last or first year it finds per grouping. Does my rough code below look reasonably correct?

Create Table #tmpWorking(

nYear int,

stMedium nchar(15)

)

Insert Into #tmpWorking(

nYear,

stMedium

)

Select DateName(Year, dtCreatedDate) As nYear,

stMedium

From ServiceCallsWithServiceAndMedium

SELECT stMedium,

CASE WHEN nYear = 2007 THEN Count (stMedium) ELSE 0 END AS [2007],

CASE WHEN nYear = 2008 THEN Count (stMedium) ELSE 0 END AS [2008],

CASE WHEN nYear = 2009 THEN Count (stMedium) ELSE 0 END AS [2009],

CASE WHEN nYear = 2010 THEN Count (stMedium) ELSE 0 END AS [2010]

FROM #tmpWorking

GROUP BY stMedium, nYear

Drop table #tmpWorking

Cheers

ab

andy behrendt SSC Enthusiast Points: 177 More actions · Answer 4

Actually I have just spotted a bug. It doesn't fix my problem but it does raise a new one which might mean I am closer to being on the right track. I have modified my code as follows:

SELECT stMedium,

Count(CASE WHEN nYear = 2007 THEN stMedium ELSE 0 END) AS [2007],

Count(CASE WHEN nYear = 2008 THEN stMedium ELSE 0 END) AS [2008],

Count(CASE WHEN nYear = 2009 THEN stMedium ELSE 0 END) AS [2009],

Count(CASE WHEN nYear = 2010 THEN stMedium ELSE 0 END) AS [2010]

This now generates the error:

Msg 245, Level 16, State 1, Line 29

Conversion failed when converting the nvarchar value 'Person ' to data type int.

I thought Count did literally that, i.e. counted the number of instances where something occurs, which in this case is the number of times nvarchar value appears in a list?

Cheers

ab

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

You can't use the aggregate inside the CASE for this. Something like the following would work...

SELECT stMedium,

SUM(CASE WHEN nYear = 2007 THEN 1 ELSE 0 END) AS [2007],

SUM(CASE WHEN nYear = 2008 THEN 1 ELSE 0 END) AS [2008],

SUM(CASE WHEN nYear = 2009 THEN 1 ELSE 0 END) AS [2009],

SUM(CASE WHEN nYear = 2010 THEN 1 ELSE 0 END) AS [2010],

FROM #tmpWorking

GROUP BY stMedium

... and, yes... I know you're trying to get a COUNT... look at the code carefully...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andy behrendt SSC Enthusiast Points: 177 More actions · Answer 6

andy behrendt

SSC Enthusiast

Points: 177

April 19, 2010 at 8:28 pm

#1154252

Many thanks guys,

This has worked a treat!

Cheers

ab

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

Thanks for the feedback, Andy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)