Re: Crosstab (Pivot) Queries for SQL Server 2005
Hi all,
I've been looking at Pivot queries for use with SQL Server 2005 and have come up with the following that may be of use to the forum. They are quite basic and have to be run from the SQL Server 2005 Management Studio (as I can't get them to run in a Recorder 6 XML Report).
Also please note, I use the heading "Vice-county" in my tables; however, in reality this is the name of the Survey.
--NMRS Count Events Per Survey Per Year Between 2000 And 2010
Use [NBNData]
Go
SELECT [Vice-county]
,[2000] AS [2000]
,[2001] AS [2001]
,[2002] AS [2002]
,[2003] AS [2003]
,[2004] AS [2004]
,[2005] AS [2005]
,[2006] AS [2006]
,[2007] AS [2007]
,[2008] AS [2008]
,[2009] AS [2009]
,[2010] AS [2010]
FROM
(SELECT SRV.ITEM_NAME AS [Vice-county]
,dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE,'Y') as [Event_Year]
,S.SAMPLE_KEY AS [Event_Key]
FROM [SAMPLE] AS S
INNER JOIN SURVEY_EVENT AS SE ON SE.SURVEY_EVENT_KEY = S.SURVEY_EVENT_KEY
INNER JOIN SURVEY AS SRV ON SRV.SURVEY_KEY = SE.SURVEY_KEY
WHERE (dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE,'Y') BETWEEN '2000' AND '2010')) evt
PIVOT
(
COUNT([Event_Key])
FOR [Event_Year] IN
([2000], [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010])
) AS pvt
--NMRS Count Records Per Species Per Year Between 2000 And 2010
Use [NBNData]
Go
SELECT [Taxon]
,[2000] AS [2000]
,[2001] AS [2001]
,[2002] AS [2002]
,[2003] AS [2003]
,[2004] AS [2004]
,[2005] AS [2005]
,[2006] AS [2006]
,[2007] AS [2007]
,[2008] AS [2008]
,[2009] AS [2009]
,[2010] AS [2010]
FROM
(SELECT ITN2.Actual_Name AS [Taxon]
,dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE,'Y') as [Year]
,TOCC.TAXON_OCCURRENCE_KEY AS [Records]
FROM Taxon_Occurrence TOCC
INNER JOIN Taxon_Determination TDET ON TDET.Taxon_Determination_key = TOCC.Taxon_Occurrence_key AND TDET.Preferred = 1
INNER JOIN Index_Taxon_Name ITN ON ITN.Taxon_List_Item_Key = TDET.Taxon_List_Item_Key
INNER JOIN Index_Taxon_Name ITN2 ON ITN2.Taxon_List_Item_Key = ITN.Recommended_Taxon_List_Item_Key
INNER JOIN [Sample] S ON S.Sample_Key = TOCC.Sample_Key
WHERE (dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE,'Y') BETWEEN '2000' AND '2010')) rec
PIVOT
(
COUNT([Records])
FOR [Year] IN
([2000], [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010])
) AS pvt
--NMRS Count Records Per Survey Per Year Between 2000 And 2010
Use [NBNData]
Go
SELECT [Vice-county]
,[2000] AS [2000]
,[2001] AS [2001]
,[2002] AS [2002]
,[2003] AS [2003]
,[2004] AS [2004]
,[2005] AS [2005]
,[2006] AS [2006]
,[2007] AS [2007]
,[2008] AS [2008]
,[2009] AS [2009]
,[2010] AS [2010]
FROM
(SELECT SRV.ITEM_NAME AS [Vice-county]
,dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE,'Y') as [Year]
,TXO.TAXON_OCCURRENCE_KEY AS [Records]
FROM TAXON_OCCURRENCE AS TXO
INNER JOIN [SAMPLE] AS S ON S.SAMPLE_KEY = TXO.SAMPLE_KEY
INNER JOIN SURVEY_EVENT AS SE ON S.SURVEY_EVENT_KEY = SE.SURVEY_EVENT_KEY
INNER JOIN SURVEY AS SRV ON SE.SURVEY_KEY = SRV.SURVEY_KEY
WHERE (dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE,'Y') BETWEEN '2000' AND '2010')) rec
PIVOT
(
COUNT([Records])
FOR [Year] IN
([2000], [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010])
) AS pvt
If anyone can use them feel free to do so. If anyone can improve on them, even better! If anyone can create new Pivot queries from these ideas, much better!
I haven't looked at the method more than adequately described by Mike Weideli in the R6XMLHLP file but all in good time.
Have a good weekend!
Senior Data Officer, National Moth Recording Scheme