SQL Pivots - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Pivots

Author

Topic

techglider
Starting Member

26 Posts

Posted - 2009-08-04 : 13:21:08

I am wondering if i should use pivots to achieve the following..

I need the following data
Similarly the results i want would be achieved by running three seperate queries but would like it to be cleaner..


SELECT count(*) AS QTR5
FROM tableC c 
JOIN tableCD cd
ON c.valueA = cd.ValueA
WHERE c.valueM = 'xxx'
AND c.qtr = 5

SELECT count(*) AS QTR6
FROM tableC c 
JOIN tableCD cd
ON c.valueA = cd.ValueA
WHERE c.valueM = 'xxx'
AND c.qtr = 6

SELECT count(*) AS QTR7
FROM tableC c 
JOIN tableCD cd
ON c.valueA = cd.ValueA
WHERE c.valueM = 'xxx'
AND c.qtr = 7

What i need to know is how can i achieve the above by doing a pivot or some other method in one query.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-04 : 13:26:09

how about this:

select QTR, count(*) as [count]
FROM tableC c
JOIN tableCD cd
ON c.valueA = cd.ValueA
WHERE c.valueM = 'xxx'
AND c.qtr in (5,6,7)
group by QTR

Be One with the Optimizer
TG

techglider
Starting Member

26 Posts

Posted - 2009-08-04 : 13:47:55

That will work.. I'm assuming pivots are used for something else then?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-04 : 14:24:25

You probably have it right. The code above will create 1 row for each QTR. PIVOT will create 1 row with a column for each QTR.



declare @t table (QTR int, tot int)
insert @t
select 5,10 union all
select 6,10 union all
select 7,10 union all
select 5,10 union all
select 6,10 union all
select 7,10

select QTR, count(*) [count]
from   @t
where  QTR in (5,6,7)
group by QTR


select p.[5] as qtr5
       ,p.[6] as qtr6
       ,p.[7] as qtr7
from   @t
PIVOT (count(tot) for QTR in ([5],[6],[7])) p 

OUTPUT:

QTR         count
----------- -----------
5           2
6           2
7           2


qtr5        qtr6        qtr7
----------- ----------- -----------
2           2           2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources