July 23, 2009 at 3:19 pm
Hello All,
please help me with this query result
select
lclog.stVal,
(CASE kt1.mcode WHEN 'I' THEN 'Interactive' WHEN 'M' THEN 'Magazine' WHEN 'N' THEN 'Newspaper' WHEN 'NET' THEN 'Network' WHEN 'O' THEN 'Outdoor' WHEN 'R' THEN 'Radio' WHEN 'S' THEN 'Supplement' WHEN 'T' THEN 'Trade' WHEN 'TV' THEN 'TV & Local Cable' WHEN 'X' THEN 'Network Radio' ELSE 'UNDEFINE' END) as Mcode
,CASE Count(*) WHEN 1 THEN 0
ELSE count(*) END as TotalDocs
from lclog left join itms on lclog.statenum = itms.statenum
left join idat i on itms.itemnum=i.itemnum left join xItem kx1 on i.itemnum=kx1.itemnum
left join kTab kt1 on kx1.keywordnum=kt1.keywordnum
where flags=12 and scope = 102 and lclog.statenum not in (101,102) and i.status <> 16
group by lclog.stVal,kt1.mcode
order by 1,2,3
Stname,MCode,TotalDocs
Precription , Trade, 3
Precription , Magazine,0
Precription , Newspaper,0
Precription , Radio, 5
Precription ,Supplement,8
Precription ,Network, Radio, 2
Precription ,Radio, 119
Precription ,TV & Local Cable, 53
StandardQue ,Network, 57
StandardQue ,Interactive,511
StandardQue ,Magazine, 31
StandardQue ,Newspaper,17
StandardQue ,Outdoor,13
StandardQue ,Supplement,7
StandardQue ,Trade,13
StandardQue ,Interactive ,0
StandardQue ,Network Radio, 0
StandardQue ,Newspaper, 0
StandardQue ,Radio, 11
StandardQue ,Supplement, 20
StandardQue ,TV & Local Cable, 28
StandardQue ,Network Radio, 48
StandardQue ,Radio, 722
StandardQue ,TV & Local Cable, 363
how can i convery the above results as below (first column values become header like a pivot table)
is it possible? in sql server
please sir kindly help me sir,
Prescription StandardQue
Trade 3 10
Magazine 40 35
Newspaper 00
Radio 1 300
Supplement 40 59
Network 554
TV& Local Cable 43 23
interactive ... ...
outdoor
.....
........
Thanks in a ton advance
asita
July 23, 2009 at 4:50 pm
with help of palmen i did this it is working fine here
SELECT mcode,
SUM(CASE WHEN stname = 'Prescription' THEN TotalDocs ELSE 0 END) AS Prescription,
SUM(CASE WHEN stname = 'StandardQue' THEN TotalDocs ELSE 0 END) AS StandardQue
FROM (
) AS T
GROUP BY mcode;
Thanks to all, it may helpful to others
best regards
July 24, 2009 at 6:17 am
You can use PIVOT operator see SQL server books online for same.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply