August 6, 2010 at 1:20 pm
lets say I have query
select empdate, count(id),category from table
group by category order by empdate
in the data I get
1/1/2010 ; 2 ; type1
1/1/2010 ; 3 ; type2
1/2/2010 ; 3 ; type1
1/3/2010 ; 4 ; type1
1/3/2010 ; 5 ; type2
1/4/2010 ; 5 ; type2
but what I really want is
date, type1, type2
1/1/2010 ; 2 ; 3
1/2/2010 ; 3 ; 0
1/3/2010 ; 4 ; 5
1/4/2010 ; 0 ; 5
Is there an easy way to get this done?
August 6, 2010 at 1:21 pm
create your first dataset and then use a pivot table
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 6, 2010 at 11:46 pm
Ur following query is incorrect...
select empdate, count(id),category from table
group by category order by empdate
Pivot is a correct option to get this kind of result
August 7, 2010 at 7:34 am
Assuming your initial query is actually
SELECT empdate, COUNT(id), category FROM tablename GROUP BY empdate, category
try this
WITH cte (empdate, idcount, category) AS
(
SELECT empdate,
COUNT(id),
category
FROM tablename
GROUP BY empdate, category
)
SELECT empdate,
COALESCE(type1, 0) AS type1,
COALESCE(type2, 0) AS type2
FROM cte
PIVOT (
MAX(idcount) FOR
category IN (type1, type2)
) AS Z
ORDER BY empdate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply