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
 Pivot from subquery (view)

Author  Topic 

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-19 : 08:44:43
Hello there.

I want to create a pivot but based on values from a subquery.

So is there a way to incorporate a subquery then use the values to pivot on.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 08:51:15
You can use a subquery - see the example below:
CREATE TABLE #tmp(colA INT, colB INT);
INSERT INTO #tmp VALUES (1,10),(2,15),(3,17);

--
SELECT * FROM
#tmp
PIVOT (MAX(colb) FOR cola IN ([1],[2],[3]))P

-- same thing as above, except it is pivoting results of a subquery
SELECT * FROM
(
SELECT colb,cola FROM #tmp
)s
PIVOT (MAX(colb) FOR cola IN ([1],[2],[3]))P

DROP TABLE #tmp
Or did you mean something like a correlated subquery? If so can you post the code?
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-19 : 08:59:53
ok more info

see my below query.

select top 10
Shortname
from Game_Classification
where [DATe] < GETDATE()
and Shortname is not null
order by [DATE] desc


this will produce the following results.

12_CC_Sur_4
12_CC_Sur_3
12_CC_Sur_2
12_CC_Sur_1
12_T20_Sou
12_ODI_Aus
12_LO_War
12_CC_Dur_4
12_CC_Dur_3
12_CC_Dur_2

now i want to use the above to pivot on for example.

select purchaseid,
[12_CC_Sur_4],
[12_CC_Sur_3],
[12_CC_Sur_2],
[12_CC_Sur_1],
[12_T20_Sou],
[12_ODI_Aus],
[12_LO_War],
[12_CC_Dur_4],
[12_CC_Dur_3],
[12_CC_Dur_2] from (
select tickets.purchaseid, game.shortname, (tickets.transactionid) from Game_Classification game
inner join Ticket_Facts tickets on
game.occasionid = tickets.OccasionID
) as query

pivot(

Count(transactionid) for Shortname
in([12_CC_Sur_4],
[12_CC_Sur_3],
[12_CC_Sur_2],
[12_CC_Sur_1],
[12_T20_Sou],
[12_ODI_Aus],
[12_LO_War], also need to use these values from subquery at the top
[12_CC_Dur_4],
[12_CC_Dur_3],
[12_CC_Dur_2])) as piv
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 09:15:27
To be able to use the PIVOT operator, you need to know in advance the list that you want in the "IN" clause of the PIVOT operator. So if you do do not know all the values that can be returned from the subquery for the shortname column, then you cannot use the PIVOT operator directly. The alternative then would be to use dynamic pivoting as described here: http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx
Go to Top of Page
   

- Advertisement -