converting row column result to row in sql

Question

I have Query like this

select convert(date,dtime) as Date, vtid,count(vtid) as count 
from Transaction_tbl 
group by cast(dtime as date),vtid 
order by  cast(dtime as date)

I am getting output lik this

Out put

Date       vtid        count
---------- ----------- -----------
2013-05-07 7           4
2013-05-08 7           5
2013-05-08 8           3
2013-05-08 9           1
2013-05-09 7           3
2013-05-12 8           1
2013-05-13 8           1
2013-05-15 7           1
2013-05-15 9           1

but i need to get out put for particular date in same row,,

Expected Output

Date       vtid        count   vtid1   count1  vtid2  count2
-------------------------------------------------------------
2013-05-07   7           4      null    null    null   null    
2013-05-08   7           5       8        3        9    1
2013-05-09   7           3       null    null    null   null  
2013-05-12   8           1       null    null    null   null

if any one know how to do please help me....

Are there a variable number of values for vtid or is it always 7, 8, or 9? — Sean, Jun 18 '13 at 07:23
actually vtid is the some location id..example if i add one more loaction then new vtid will create as 10 — , Jun 18 '13 at 07:27
if you have fixed/small cardinality of vtid column then you can do it using a single sql query. Otherwise you need to use procedure — chetan, Jun 18 '13 at 07:29

Sean · Answer 1 · 2013-06-18T09:27:46.863

0

This is probably not as dynamic as you were hoping, but it will produce something similar to your expected output for vtid 7, 8, and 9.

select d.Date, v7.vtid, v7.Count, v8.vtid, v8.Count, v9.vtid, v9.Count
from (select distinct convert(date,dtime) as Date from Transaction_tbl) as d
  left outer join (select convert(date,dtime) as Date, vtid, count(vtid) as Count from Transaction_tbl where vtid = 7 group by convert(date,dtime), vtid) as v7 on v7.Date = d.Date
  left outer join (select convert(date,dtime) as Date, vtid, count(vtid) as Count from Transaction_tbl where vtid = 8 group by convert(date,dtime), vtid) as v8 on v8.Date = d.Date
  left outer join (select convert(date,dtime) as Date, vtid, count(vtid) as Count from Transaction_tbl where vtid = 9 group by convert(date,dtime), vtid) as v9 on v9.Date = d.Date
order by d.Date

Full disclosure: I did not attempt to run this, there may be a typo in there

Edit: Though this might not produce output in a format that works for you, a pivot query is a bit cleaner and easier to modify

select *
from (
  select vtid, convert(date, create_date) as Date
  from Transaction_tbl
  where locid = 5
) as vt
pivot (
  count(vtid)
  for vtid in ([7], [8], [9])
) as pvt

edited Jun 18 '13 at 09:27

answered Jun 18 '13 at 07:36

Sean

4,365
1
27
31

Realized I forgot the "on" clauses – Sean Jun 18 '13 at 07:48
I didnt Get you,,,i run this query in sql server,,but i am getting same error – Jun 18 '13 at 07:51
Column 'Transaction_tbl.vtid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Jun 18 '13 at 07:56
If you want to save that query as a procedure, you can just prepend "create proc MyProcName as" to the query, if my memory is correct. Then you could run it with "exec MyProcName". Of course, MyProcName could be whatever you want. – Sean Jun 18 '13 at 08:07
In above query i have to give one more filtering " i want to add where locid=5" ,,,so where i can add this? – Jun 18 '13 at 08:27
left outer join (select convert(date,dtime) as Date, vtid, count(vtid) as Count from Transaction_tbl where vtid = 7 and Locid=5 group by convert(date,dtime), vtid) as v7 on v7.Date = d.Date – Jun 18 '13 at 08:50
while executing your last query am getting out put like this – Jun 18 '13 at 09:38
Date 7 8 9 10 11 ---------- ----------- ----------- ----------- ----------- ----------- 2013-05-07 4 0 0 0 0 2013-05-08 5 3 1 0 0 2013-05-09 3 0 0 0 0 2013-05-12 0 1 0 0 0 now column name is showing 7,8,9, insted of 7,8,9 how i can give normal,vip,vvip – Jun 18 '13 at 09:39
i mean how i can change the column name,,,now i am getting column name 7,8,9,,,,,i want change the column name as normal,vip,vvip, – Jun 18 '13 at 09:45
Sorry, didn't see your last posts until now. Hope you've figured out the rest. If you found this answer helpful, please mark it as accepted. Thanks! – Sean Jun 19 '13 at 22:48

score 0 · Answer 2 · edited May 23 '17 at 12:05

What you really need is a PIVOT query with dynamic columns. There are several answers here on SO showing examples of this. Take a look at this, or this or just google it. The difference you'll get from your desired result set is something like the following (numbers might be slightly out as I only took a cursory glance at your table above, but you'll get the idea);

Date       vtid-7     vtid-8      vtid-9   
2013-05-07   4           null      null
2013-05-08   5           3         1
2013-05-09   3           null      null
2013-05-12   null        1         null
2013-05-13   null        1         null
2013-05-15   1           null      1

converting row column result to row in sql

Out put

2 Answers2