Cross Tab Query

Question

Post reply

Cross Tab Query

khurram sultan

SSC-Addicted

Points: 485
More actions
December 8, 2007 at 2:44 am

#69218

Any One Here Can Show Me Some Expamples Of Cross Tab Quries

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

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 1

Look in the Index of Books Online for "cross-tab reports"... good example there.

--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)

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 2

IN sql 2005, you can also look up the PIVOT operator. In SQL 2000, you still had to use the CASE statement as follows:

Select

SalesPersonID,

JanSales = Sum(Case when month(SaleDate) = 1 Then SaleAmt Else 0 End),

FebSales = Sum(Case when month(SaleDate) = 2 Then SaleAmt Else 0 End),

MarSales= ...

From SalesTable

Group By SalesPersonID

The disadvantage to this approach is that you need to know your columns ahead of time. There are other strategies I'm sure to get dynamic columns this way, but it usually involves building dynamic sql strings.

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

Jeremy,

Do you have an example of how you'd solve the above using the Pivot operator?

--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)

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 4

Jeff,

Thanks for the interesting challenge. I mentioned the PIVOT operator, because I had seen it in the docs as a cross tab operator. However, it is something relatively new, and most of us like to stay with the tried and true unless presented with a viable alternative that really makes my life easier. For this sample, I stole the [Sales].[vSalesPersonSalesByFiscalYears] view, and modified it for my purposes.

Here is the result:

Use AdventureWorks

GO

SELECT

pvt.[SalesPersonID]

,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

FROM (SELECT

soh.[SalesPersonID]

,soh.[SubTotal]

,case month(OrderDate) when 1 Then 'Jan'

when 2 Then 'Feb'

when 3 Then 'Mar'

when 4 Then 'Apr'

when 5 Then 'May'

when 6 Then 'Jun'

when 7 Then 'Jul'

when 8 Then 'Aug'

when 9 Then 'Sep'

when 10 Then 'Oct'

when 11 Then 'Nov'

when 12 Then 'Dec' end AS [MonthName]

FROM [Sales].[SalesPerson] sp

INNER JOIN [Sales].[SalesOrderHeader] soh

ON sp.[SalesPersonID] = soh.[SalesPersonID]

Where Year(soh.OrderDate) = 2002

) AS soh

PIVOT

(

SUM([SubTotal])

FOR [MonthName]

IN([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])

) AS pvt

order by salespersonid

--as compared to "the old way"

SELECT

soh.[SalesPersonID]

,Jan = sum(case when month(orderdate) = 1 Then SubTotal Else 0 End)

,Feb = sum(case when month(orderdate) = 2 Then SubTotal Else 0 End)

,Mar = sum(case when month(orderdate) = 3 Then SubTotal Else 0 End)

,Apr = sum(case when month(orderdate) = 4 Then SubTotal Else 0 End)

,May = sum(case when month(orderdate) = 5 Then SubTotal Else 0 End)

,Jun = sum(case when month(orderdate) = 6 Then SubTotal Else 0 End)

,Jul = sum(case when month(orderdate) = 7 Then SubTotal Else 0 End)

,Aug = sum(case when month(orderdate) = 8 Then SubTotal Else 0 End)

,Sep = sum(case when month(orderdate) = 9 Then SubTotal Else 0 End)

,Oct = sum(case when month(orderdate) = 10 Then SubTotal Else 0 End)

,Nov = sum(case when month(orderdate) = 11 Then SubTotal Else 0 End)

,Dec = sum(case when month(orderdate) = 12 Then SubTotal Else 0 End)

FROM [Sales].[SalesPerson] sp

INNER JOIN [Sales].[SalesOrderHeader] soh

ON sp.[SalesPersonID] = soh.[SalesPersonID]

Where Year(soh.OrderDate) = 2002

group by soh.SalesPersonID

order by salespersonid

I compared query plans, and while slightly different, it was a 50/50 split in computation power. Also, the I/O looked the same, so I'm not completely sold on the PIVOT operator yet. I think it looks kind of clunky. Then again, we usually say that when we come across something new. Perhaps I'll force myself to use it a few times, and it will grow on me.

What are your thoughts on this?

-Jeremy

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

so I'm not completely sold on the PIVOT operator yet. I think it looks kind of clunky. Then again, we usually say that when we come across something new. Perhaps I'll force myself to use it a few times, and it will grow on me.

Exactly... in fact, Matt Miller, Sergiy, and I did some extreme testing on some of the other "new" functionality... although the new stuff is frequently very easy to write, it is also frequently performance challenged.

--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)

rnjohnson10 SSC Journeyman Points: 96 More actions · Answer 6

I don't see the advantage of using PIVOT rather than the tried and true sum(Case when.... method. You still need to specify the columns in either case. Reading the sum(Case when... code is a lot easier for me anyway.

Ah well, maybe they'll just get it straight in some future version. They manage true cross tabs in Access, so it's not like they don't know how to make it work.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

I was a bit disappointed by it as well after the testing. The syntax is cleaner, but it's limiting (only on specific type of operation per PIVOT statement for one). Second aggregation requires a self-join, a seond pass on the data, joining stuff which is no longer indexed, etc....It doesn't seem to bring much to the table, for all of the restrictions it requires.

Also - I'm a bit surprised by the "IO's are the same" - they looked quite a bit higher here.

Hopefully 2008 can get these fixed up a bit. UNPIVOT is fairly cute, though (assuming you get slammed with having to maintain not fully normalized data...)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?