how to see query results in pivot table like struc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to see query results in pivot table like struc

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-23 : 17:14:38

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
dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-23 : 18:49:49

i tried palmens suggestion as

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 (
<your current query here without ORDER BY>
) AS T
GROUP BY mcode;

with this i got the result

it may help to others

thanks to alll

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-23 : 19:10:02

is there any way to show totals @ bottom

for ex

Prescription StandardQue
Trade 3 10
Magazine 40 35
Newspaper 00
Radio 1 300
TOTAL 44 345

please

Thanks in advance
dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-23 : 19:58:37

Please help me to find out

is it possible?

please.....

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-24 : 00:15:17

Use Reporting Services Layout to figure out that one.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-24 : 03:19:54

HI Try this once,


declare @temp table ( Stname varchar(32), MCode varchar(32) ,TotalDocs int )
insert into @temp select 'Precription' , 'Trade', 3
insert into @temp select 'Precription' , 'Magazine',0
insert into @temp select 'Precription' , 'Newspaper',0
insert into @temp select 'Precription' , 'Radio', 5
insert into @temp select 'Precription' , 'Supplement',8
insert into @temp select 'Precription' , 'Network Radio', 2
insert into @temp select 'Precription' , 'TV & Local Cable', 53
insert into @temp select 'StandardQue' , 'Network', 57
insert into @temp select 'StandardQue' , 'Magazine', 31
insert into @temp select 'StandardQue' , 'Newspaper',17
insert into @temp select 'StandardQue' , 'Outdoor',13
insert into @temp select 'StandardQue' , 'Supplement',7
insert into @temp select 'StandardQue' , 'Trade',13
insert into @temp select 'StandardQue' , 'Interactive' ,0
insert into @temp select 'StandardQue' , 'Radio', 11
insert into @temp select 'StandardQue' , 'TV & Local Cable', 28
insert into @temp select 'StandardQue' , 'Network Radio', 48

--SELECT * FROM @TEMP

SELECT mcode,Precription,StandardQue from @Temp
pivot ( min(totaldocs) for Stname in (Precription,StandardQue)) p

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-24 : 03:28:50

Expading Nageswars solution you can get the final total like this


declare @temp table ( Stname varchar(32), MCode varchar(32) ,TotalDocs int )
insert into @temp select 'Precription' , 'Trade', 3
insert into @temp select 'Precription' , 'Magazine',0
insert into @temp select 'Precription' , 'Newspaper',0
insert into @temp select 'Precription' , 'Radio', 5
insert into @temp select 'Precription' , 'Supplement',8
insert into @temp select 'Precription' , 'Network Radio', 2
insert into @temp select 'Precription' , 'TV & Local Cable', 53
insert into @temp select 'StandardQue' , 'Network', 57
insert into @temp select 'StandardQue' , 'Magazine', 31
insert into @temp select 'StandardQue' , 'Newspaper',17
insert into @temp select 'StandardQue' , 'Outdoor',13
insert into @temp select 'StandardQue' , 'Supplement',7
insert into @temp select 'StandardQue' , 'Trade',13
insert into @temp select 'StandardQue' , 'Interactive' ,0
insert into @temp select 'StandardQue' , 'Radio', 11
insert into @temp select 'StandardQue' , 'TV & Local Cable', 28
insert into @temp select 'StandardQue' , 'Network Radio', 48

--SELECT * FROM @TEMP

select case when GROUPING(mcode)=1 then 'Total' else mcode end,sum(Precription),sum(StandardQue) from
(
SELECT mcode,Precription,StandardQue from @Temp
pivot ( min(totaldocs) for Stname in (Precription,StandardQue)) p
)t group by mcode with rollup

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-24 : 09:35:48

Thank you very much for your help i will try it and let you know

thanks for your guidance

it is highly appreciatable

i will let you know , once i get it done

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-24 : 15:20:12

Dear All,

i modified my query as below but i am getting below error please help me

<<QUERY removed for security reasons>>

----- Result as below

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'pivot'.
Msg 319, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

could you please help me , i am in the final stage of my problem

Please sir

Thanks in advance
dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-24 : 15:34:24

is it issue with inside group by clause just before PIVOT keyword?

please.....

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-24 : 16:16:05

Any Ideas or Suggestions please kindly

Regards
dhani

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-26 : 07:10:12