Cross Tab for Unknown Columns

[livezone]

Hi,

The following query qives information in a cross tab format. Here You know the total number of columns as Q1, Q2, Q3, Q4.

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year

How you can make a cross tab where you donot know in advance how many columns it will be produce as column. Similiar to MS Acess crosstab queries. Please Guide
Thanks
Shafiq

 

[vongrunt]

General idea:

- compose vanilla aggregated SELECT:

SELECT YEAR, Quarter, COUNT(*) FROM Northwind.dbo.Pivot GROUP BY Year, Quarter ORDER BY Year, Quarter

- insert these results into temp table
- create crosstab table with Year column
- insert all distinct Year values from temp table
- for each distinct Quarter value from temp table
- add column to crosstab table, make it's default 0 if necessary
- update column with values from temp table (inner join!)

- SELECT * from crosstab table
- drop everything

Or you can build entire crosstab query with dynamic SQL. Check faq183-5269 and faq183-5278 for more information.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[livezone]

Hi,

I have used the given FAQ, but the problem is that it does not put ' in the Case / When area

Here is the sample

EXEC spCrossTabPrint
'Select DMName from V_Usage u inner join dbo.KioskList kl ON u.useHostName = kl.KioskName inner Join Stores.dbo.Store s ON u.Store = s.StoreNum inner join Stores.dbo.District d on s.District = d.DistrictID Group by d.DMNAME Order by 1 ',
'Sum(Visitors)',
'Left(DATENAME(m,useDate),3)'

Output is shown below, when u run this in query analyzer, it will complain on WHEN Jul it should be WHEN 'Jul'

SELECT DMName,
[Jul] = Sum(CASE Left(DATENAME(m,useDate),3) WHEN Jul THEN Visitors END),
[Jun] = Sum(CASE Left(DATENAME(m,useDate),3) WHEN Jun THEN Visitors END),
[May] = Sum(CASE Left(DATENAME(m,useDate),3) WHEN May THEN Visitors END)
FROM V_Usage u inner join dbo.KioskList kl ON u.useHostName = kl.KioskName inner Join Stores.dbo.Store s ON u.Store = s.StoreNum inner join Stores.dbo.District d on s.District = d.DistrictID
GROUP BY d.DMNAME
ORDER BY 1

Thanks
Shafiq