group data in columns not rows
-
Dear All,
I have table with the following data:
----------------------------------------------
User_ID opDateTypeApp_No
----------------------------------------------
user11/1/2005HIV1
user11/1/2005HIV2
user11/1/2005HBs1
user11/1/2005HBs2
user11/1/2005HBs3
----------------------------------------------
I want to write a query that will count the number of applications for each type for every day , but instead of
using group by I want the count to appear in columns not rows.
for example if i used group by clause like in the following query:
select user_id,opdate,type,count(*)
from table1
group by user_id,opdate,type
the resultant will be:
-----------------------------------------------
User_IDopDateTypeCount
-----------------------------------------------
user11/1/2005HIV2
user11/1/2005HBs3
-----------------------------------------------
but I want the result like:
---------------------------------------------------------------
User_ID opDateHIV_CountHBS_Count
---------------------------------------------------------------
user11/1/200523
---------------------------------------------------------------
any help for writing such query?
-
Hi,
Get the output in to one temp table like
select user_id,opdate,type,count(*) as Count into #temp
from table1
group by user_id,opdate,type
Select User_ID,opDate,
HIV_Count = Case When Type ='HIV' then Count Else 0 End,
HBS_Count = CASE When Type ='HBS' then Count else 0 end
from #temp
Group by User_ID,Opdate
Hope this will work for u.
Regards,
Ramesh K
-
Alternatively, use the following:
select
user_id,
opdate,
sum(case when Type = 'HIV' then 1 else 0 end) as HIV_Count,
sum(case when Type = 'HBs' then 1 else 0 end) as HBS_Count
from table1
group by user_id, opdate
-
really thank you (specially jesper)
but because I am greedy, is there a way to make it more dynamic. I mean if I didn't even know the types (in this case even the number of columns will not be known before running the query).
-
I suppose you could define a cursor over "select distinct Type from table1", use this to build a string with the SQL statement you are after, and then dynamically execute this with exec() (let me know if you want more details). Instead I think I would execute
select user_id,opdate,type,count(*)
from table1
group by user_id,opdate,type
and let the client application sort it out
-
sounds complicated. specially if i am gonna use it in many tables..
fortunately for me, the first answer is suffecient for me now.
thank you all
-
sounds complicated. specially if i am gonna use it in many tables..
fortunately for me, the first answer is suffecient for me now.
thank you all
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply