1 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 382

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!

Les Evans-Hill
Senior Data Officer, National Moth Recording Scheme

nmrs's Website

2 Reply by Graham French (edited by Graham French 07-12-2009 10:22:23)

  • Graham French
  • Administrator
  • Offline
  • Registered: 23-05-2006
  • Posts: 430

Re: Crosstab (Pivot) Queries for SQL Server 2005

Hello Les

Coincidentally I have just been using Pivot queries in SQL server 2005 to query the NBN Gateway database. As I don't know what values are in the column used to pivot the table I need to generate the resulting column names dynamically. Thought the SQL to do this may be of use to you so have tried to convert "NMRS Count Events Per Survey Per Year Between 2000 And 2010" SQL to do this. It may need tweaking but will give you an idea.

The SQL basically
1. Gets the records and places in a temporary table
2. Get the unique values in the pivot column  to use as final column outputs,  puts this is into a string stored in a variable called @columns
3. Creates the query to run the Pivot query incorporating the required column names, puts this query into a variable called @query
4. Executes this query
4. Drops the temporary table

Heres the SQL

Use [NBNData]
Go

DECLARE @columns VARCHAR(4000)
DECLARE @query VARCHAR(4000)

-- Select record
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]
INTO #temptable
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'

-- Select year columns in EventYear
SELECT @columns = COALESCE(@columns + ',[' + cast(Event_Year as varchar) + ']',
'[' + cast(Event_Year as varchar)+ ']')
FROM #temptable
GROUP BY Event_Year

-- PIVOT temp table on Event_Year column
SET @query = '
select *
from #temptable
pivot
(
Count(Event_Key)
for [Event_Year] in (' + @columns + ')
)
as p'

EXECUTE(@query)

Drop table #temptable

Hope this helps if you need to run cross table (pivot) querys in SQL 2005 and don't want to predetermine the columns beforehand

Best wishes

Graham
NBN Technical Liaison Officer

Graham French's Website

3 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 382

Re: Crosstab (Pivot) Queries for SQL Server 2005

Hi Graham,

This looks great! I'll take a look today once I've exported and sent (to you!) the latest NMRS database NBN Gateway export.

Best wishes,
Les.

Les Evans-Hill
Senior Data Officer, National Moth Recording Scheme

nmrs's Website

4 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Crosstab (Pivot) Queries for SQL Server 2005

Les

The reason why your query will not run in XML reports is because you have used a sub query. XML reports normally assume that the last SELECT statement is the actual one to use for the report.  Subqueries can be used in the final query which generates the report, but the FROM in the subquery must be encased as follows /**/FROM/**/.

Mike Weideli

MikeWeideli's Website