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
No comments:
Post a Comment