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.
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 subquerySELECT * FROM ( SELECT colb,cola FROM #tmp)s PIVOT (MAX(colb) FOR cola IN ([1],[2],[3]))PDROP TABLE #tmp Or did you mean something like a correlated subquery? If so can you post the code? |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-02-19 : 08:59:53
|
ok more infosee my below query.select top 10 Shortname from Game_Classification where [DATe] < GETDATE()and Shortname is not nullorder by [DATE] descthis will produce the following results.12_CC_Sur_412_CC_Sur_312_CC_Sur_212_CC_Sur_112_T20_Sou12_ODI_Aus12_LO_War12_CC_Dur_412_CC_Dur_312_CC_Dur_2now 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 gameinner join Ticket_Facts tickets ongame.occasionid = tickets.OccasionID) as querypivot(Count(transactionid) for Shortnamein([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 |
|
|
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 |
|
|
|
|
|
|
|