You are not logged in.

Announcement

Welcome to the forum for discussion of NBN technical issues. If you are new and want some guidance on getting started view the help page. Be sure to read this if you need help on setting up Email Digests, which will allow you to recieve forum updates via email. This forum is dedicated to the various aspects of the NBN including Recorder biological recording software, Indicia and the NBN Gateway as well as the needs of the NBN technical group.

Pages: 1

 

#1 2009-12-04 15:37:30

nmrs
Veteran
From: Lulworth, Dorset
Registered: 2006-11-22
Posts: 96
Website

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.

Code:

--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

Code:

--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

Code:

--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 Hill.
Data Manager, National Moth Recording Scheme
Butterfly Conservation

Offline

 

#2 2009-12-07 10:20:22

Graham French
Moderator
Registered: 2006-05-23
Posts: 82

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

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

Offline

 

#3 2009-12-07 10:29:26

nmrs
Veteran
From: Lulworth, Dorset
Registered: 2006-11-22
Posts: 96
Website

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 Hill.
Data Manager, National Moth Recording Scheme
Butterfly Conservation

Offline

 

#4 2009-12-07 11:52:35

MikeWeideli
Veteran
From: Littlefield Consultancy
Registered: 2006-07-05
Posts: 128
Website

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
JNCC Recorder Approved Expert

Offline

 

 

Pages: 1

Board footer

Powered by PunBB
© Copyright 2002–2008 PunBB