Crosstab queries

Dear friends,

I wonder if exist a way to make crosstab queries in SQL Server
like those in Access without "external" programming. Does the
SQL Server supports the "TRANSFORM" SQL-extension?

Thanks in advance, Sotiris.Sotiris Rentoulis (rentoulis@.hotmail.com) writes:
> I wonder if exist a way to make crosstab queries in SQL Server
> like those in Access without "external" programming. Does the
> SQL Server supports the "TRANSFORM" SQL-extension?

No.

There is no particular support for pivot tables in SQL 2000. SQL 2005,
currently in beta, comes with a PIVOT operator. It still only supports
static pivot tables.

Here is a simple example of a static crosstab in SQL 2000:

SELECT product,
Q1 = SUM(CASE datepart(month, salesdate) / 3 WHEN 0 THEN amt END),
Q2 = SUM(CASE datepart(month, salesdate) / 3 WHEN 1 THEN amt END),
Q3 = SUM(CASE datepart(month, salesdate) / 3 WHEN 2 THEN amt END),
Q4 = SUM(CASE datepart(month, salesdate) / 3 WHEN 3 THEN amt END)
FROM sales
GROUP BY product

Dynamic crosstabs requires you to write dynamic SQL. Note that dynamic
crosstabs, how useful they be, do not really fit into the relational
model. A popular tool for dynamic crosstab is RAC, see
http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

cross table

Sir,

My query is

select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/11/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/12/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/13/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/14/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/15/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/16/2006'
group by task_date

My out put is

Date Program Design Upload Testing Meeting Others

2006-09-11 00:00:00.000 42 0 0 8 2 1

2006-09-12 00:00:00.000 77 0 0 4 0 0

2006-09-13 00:00:00.000 56 0 0 8 0 1

2006-09-14 00:00:00.000 63 0 0 6 0 1

2006-09-15 00:00:00.000 63 0 0 6 0 1

Now i want in below format

2006-09-11 2006-09-11 etc

Program 42 77

Design 0 0

Upload 0 0

Testing 8 4

Meeting 2 0

Others 1 0

Total 53 81

How to convert in this format .

--From your output as table:transposetable$:

SELECT ISNULL(cat, 'Total') ,SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',

SUM([9/15/2006]) AS '9/15/2006'

FROM (SELECT cat, MIN(CASE WHEN t.tDate = '9/11/2006' THEN Program END) AS '9/11/2006',

MIN(CASE WHEN t.tDate = '9/12/2006' THEN Program END) AS '9/12/2006',

MIN(CASE WHEN t.tDate = '9/13/2006' THEN Program END) AS '9/13/2006',

MIN(CASE WHEN t.tDate = '9/14/2006' THEN Program END) AS '9/14/2006',

MIN(CASE WHEN t.tDate= '9/15/2006' THEN Program END) AS '9/15/2006'

FROM

(SELECT 'Program' as cat, tDate, Program FROM transposetable$

UNION ALL

SELECT 'Design', tDate, Design FROM transposetable$

UNION ALL

SELECT 'Upload',tDate, Upload FROM transposetable$

UNION ALL

SELECT 'Testing', tDate, Testing FROM transposetable$

UNION ALL

SELECT 'Meeting', tDate, Meeting FROM transposetable$

UNION ALL

SELECT 'Others', tDate, Others FROM transposetable$

) t

GROUP BY cat ) x

GROUP BY cat

WITH ROLLUP

|||

--SQL Server 2005, Louis's idea. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

WITH myCTE as(

select tDate, cat, value

from ( select tDate, Program, Design, Upload, Testing, Meeting,Others

from transposetable$) p

UNPIVOT

(value for cat in ([Program], [Design], [Upload],[Testing],[Meeting],[Others])) as unpvt)

SELECT ISNULL(cat, 'Total'),SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',SUM([9/15/2006]) AS '9/15/2006'

FROM

(select cat, tDate,value

from myCTE) as rotated

PIVOT

(SUM(value) FOR tDate in ([9/11/2006],[9/12/2006],[9/13/2006],[9/14/2006],[9/15/2006])) as pvt

GROUP BY cat

WITH ROLLUP

|||

Hi,

have you looked at the UNPIVOT function?

Sten-Gunnar