SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot from subquery (view)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 02/19/2013 :  08:44:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/19/2013 :  08:51:15  Show Profile  Reply with Quote
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?

Edited by - James K on 02/19/2013 08:51:42
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 02/19/2013 :  08:59:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/19/2013 :  09:15:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000