March 10, 2010 at 3:11 am
Hi
I have a requirement as follows
Table 1: status_tbl
status name
1 ok
2 not ok
3 bad
4 good
....
....
...
Table2: user_tbl
Username status
abd 1
ggdhf 2
gdfhgd 1
abd 2
status is Fk for table2 (user_tbl)
I would like to have the query o/p as
note: column should contain all the available status, row should have user name and for the all status count should be shown for that user
username 1 2 3 4
abd 1 1 0 0
ggdhf 0 1 0 0
gdfhgd 1 0 0 0
Any help is appreciated
March 10, 2010 at 3:14 am
You could use pivot ,
but i would prefer to sum a condition.
So something like this ?
select username,
sum(case when Status = 1 then 1 else 0 end),
sum(case when Status = 2 then 1 else 0 end),
sum(case when Status = 3 then 1 else 0 end),
sum(case when Status = 4 then 1 else 0 end)
from user_tbl
group by username
March 10, 2010 at 3:22 am
yes, i have tried using Pivot but I do not know what the status can be .. hence cannot hard code status in either Sum or Pivot..
any other way.
My sample Pivot query
SELECT username] as newstat,
[4], [3], [2], [1]
FROM
(SELECT stat.status,us.username
FROM status_tbl stat left outer join user_tbl us
on stat.status= us.status) AS SourceTable
PIVOT
(
count([status])
FOR Status IN ([4], [3], [2], [1])
) AS PivotTable;
March 10, 2010 at 3:29 am
This can be achieved by using dynamic sql , but i would only do this as a last resort , if this cannot be done at the front end.
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply