April 26, 2011 at 11:32 am
Hi all,
I got most of the way on this problem at the MSDN T-SQL forums, but now I have one last problem to overcome.
Goal: convert vertical data of daily Pollen Counts for (up to) 25 Allergens to a horizontal dataset (list each Allergen that has a positive count in one row for each day).
Example Tables and Data:
Create PCM:
USE [AllergyCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE PCM (
[Record_Date] [datetime] NOT NULL,
[PCM] [int] NULL,
[AllergyID] [int] NULL,
[PCMID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_PCM_1] PRIMARY KEY CLUSTERED
(
[PCMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Create Allergens:
USE [AllergyCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE Allergens (
[AllergyID] [int] IDENTITY(1,1) NOT NULL,
[AllergenName] [varchar](50) NULL,
CONSTRAINT [PK_Allergens] PRIMARY KEY CLUSTERED
(
[AllergyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Insert Data for PCM:
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',93,5)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',62,10)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,11)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',1627,14)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,15)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,19)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',31,24)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,25)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,3)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,4)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,9)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',356,16)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',46,20)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,2)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,7)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',3736,8)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',16,13)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,21)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,22)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',31,1)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,6)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,12)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',186,17)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,18)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/2/2011 12:00:00 AM',0,23)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,23)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,18)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',155,17)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,12)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',16,6)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,1)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,22)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,21)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',16,13)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',3550,8)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,7)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,2)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',31,20)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',124,16)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,9)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,4)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,3)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,25)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,24)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,19)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,15)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',682,14)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,11)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',46,10)
INSERT INTO PCM (Record_Date,PCM,AllergyID) VALUES ('4/1/2011 12:00:00 AM',0,5)
Insert Data for Allergens:
INSERT INTO Allergens (AllergenName) VALUES ('Ash')
INSERT INTO Allergens (AllergenName) VALUES ('Birch')
INSERT INTO Allergens (AllergenName) VALUES ('Cedar')
INSERT INTO Allergens (AllergenName) VALUES ('Cedar_Elm')
INSERT INTO Allergens (AllergenName) VALUES ('Cotton_Wood')
INSERT INTO Allergens (AllergenName) VALUES ('Elm')
INSERT INTO Allergens (AllergenName) VALUES ('English_Plantain')
INSERT INTO Allergens (AllergenName) VALUES ('Fungus')
INSERT INTO Allergens (AllergenName) VALUES ('General_Weeds')
INSERT INTO Allergens (AllergenName) VALUES ('Grass')
INSERT INTO Allergens (AllergenName) VALUES ('Hackberry')
INSERT INTO Allergens (AllergenName) VALUES ('Hickory')
INSERT INTO Allergens (AllergenName) VALUES ('Juniper')
INSERT INTO Allergens (AllergenName) VALUES ('Maple')
INSERT INTO Allergens (AllergenName) VALUES ('Mountain_Cedar')
INSERT INTO Allergens (AllergenName) VALUES ('Mulberry')
INSERT INTO Allergens (AllergenName) VALUES ('Oak')
INSERT INTO Allergens (AllergenName) VALUES ('Pecan')
INSERT INTO Allergens (AllergenName) VALUES ('Pigweed')
INSERT INTO Allergens (AllergenName) VALUES ('Pine')
INSERT INTO Allergens (AllergenName) VALUES ('Ragweed')
INSERT INTO Allergens (AllergenName) VALUES ('Russian_Thistle')
INSERT INTO Allergens (AllergenName) VALUES ('Sycamore')
INSERT INTO Allergens (AllergenName) VALUES ('Walnut')
INSERT INTO Allergens (AllergenName) VALUES ('Willow')
Current SQL (which includes all Allergens (columns) even if they have a zero value) (Thanks to Naomi on MSDN T-SQL Forum):
declare @Cols nvarchar(max), @Cols0 nvarchar(max),
@Total nvarchar(max), @SQL nvarchar(max)
select @Cols = stuff((select ', ' + quotename(A.AllergenName) from
Allergens A JOIN PCM P on A.AllergyID =
P.AllergyID group by A.AllergenName
ORDER by A.AllergenName FOR XML PATH('')),1,2,'')
select @Cols0 = (select ', coalesce(' + quotename(A.AllergenName) + ',0) as ' + quotename(A.AllergenName)
from
Allergens A JOIN PCM P on A.AllergyID =
P.AllergyID group by A.AllergenName
ORDER by A.AllergenName FOR XML PATH(''))
select @Total = stuff((select ' + coalesce(' + quotename(A.AllergenName) + ',0)'
from
Allergens A JOIN PCM P on A.AllergyID =
P.AllergyID group by A.AllergenName
ORDER by A.AllergenName FOR XML PATH('')),1,2,'')
set @SQL = 'select Record_Date ' + @Cols0 + ' , ' +
@Total + ' as Total from
(select P.PCM, A.AllergenName, P.Record_Date FROM PCM P JOIN Allergens A ON P.AllergyID = A.AllergyID WHERE Record_Date BETWEEN ''04/01/2011'' AND ''04/30/2011'')
src PIVOT (sum(PCM) FOR AllergenName IN (' + @Cols + ')) pvt'
print @SQL -- verify
execute (@SQL)
I'm stumped getting the PIVOT to ONLY include "columns" that have a value > 0. For instance in the sample data, Allergen Mountain Cedar (and several others) have a zero count for the entire date span - and should not be included in the result set.
Any help would be appreciated, even a "can't be done"...
Thanks.
April 26, 2011 at 12:10 pm
in your query to select the column names... you will want to filter out the ones that have no values during that date period otherwise they'll be present in your column list. So you'll need the where clause on that query as well.
April 26, 2011 at 12:13 pm
To be more exact, these statements... notice the where clause...
select @Cols = stuff((select ', ' + quotename(A.AllergenName) from
Allergens A JOIN PCM P on A.AllergyID =
P.AllergyID
WHERE p.Record_Date BETWEEN '04/01/2011' AND '04/30/2011'
group by A.AllergenName
ORDER by A.AllergenName FOR XML PATH('')),1,2,'')
select @Cols0 = (select ', coalesce(' + quotename(A.AllergenName) + ',0) as ' + quotename(A.AllergenName)
from
Allergens A JOIN PCM P on A.AllergyID =
P.AllergyID
WHERE p.Record_Date BETWEEN '04/01/2011' AND '04/30/2011'
group by A.AllergenName
ORDER by A.AllergenName FOR XML PATH(''))
April 26, 2011 at 1:36 pm
That does not filter on what becomes the "column's" of (Ash, Birch, Cedar, etc.), it filters on the date, which i'm already achieving with the last WHERE statement in the PIVOT section. Tried implementing what you suggested and nothing changed...
I can't see anything in what you posted that would filter results based (for example) no instances of Allergens recorded during the specified time period for "Mountain Cedar" or, AllergyID 15...
Maybe a visual example with Letters indicating individual allergens?
Date A B C D E F G H
0401 0 1 1 9 8 0 0 0
0402 0 1 2 7 8 0 0 0
0403 0 1 4 8 9 0 0 0
In the above example, columns A, F, G and H should be excluded from result list because between 04/01 and 04/03 no values above 0 were recorded...
Does that make sense?
April 27, 2011 at 6:57 am
Add a HAVING clause to each of your selects... you have to sum the PCM and make sure it is >0 to include it, as well as using the WHERE clause in each SELECT.
declare @Cols nvarchar(max), @Cols0 nvarchar(max),
@Total nvarchar(max), @SQL nvarchar(max)
select @Cols = stuff((select ', ' + quotename(A.AllergenName) from
Allergens A JOIN PCM P on A.AllergyID =
P.AllergyID
WHERE Record_Date BETWEEN '04/01/2011' AND '04/30/2011'
group by A.AllergenName
HAVING SUM(p.PCM) > 0
ORDER by A.AllergenName FOR XML PATH('')),1,2,'')
select @Cols0 = (select ', coalesce(' + quotename(A.AllergenName) + ',0) as ' + quotename(A.AllergenName)
from
Allergens A JOIN PCM P on A.AllergyID = P.AllergyID
WHERE Record_Date BETWEEN '04/01/2011' AND '04/30/2011'
group by A.AllergenName
HAVING SUM(p.PCM) > 0
ORDER by A.AllergenName FOR XML PATH(''))
select @Total = stuff((select ' + coalesce(' + quotename(A.AllergenName) + ',0)'
from
Allergens A JOIN PCM P on A.AllergyID =
P.AllergyID
WHERE Record_Date BETWEEN '04/01/2011' AND '04/30/2011'
group by A.AllergenName
HAVING SUM(p.PCM) > 0
ORDER by A.AllergenName FOR XML PATH('')),1,2,'')
set @SQL = 'select Record_Date ' + @Cols0 + ' , ' +
@Total + ' as Total from
(select P.PCM, A.AllergenName, P.Record_Date FROM PCM P JOIN Allergens A ON P.AllergyID = A.AllergyID WHERE Record_Date BETWEEN ''04/01/2011'' AND ''04/30/2011'')
src PIVOT (sum(PCM) FOR AllergenName IN (' + @Cols + ')) pvt'
print @SQL -- verify
execute (@SQL)
April 27, 2011 at 12:22 pm
AWESOME! Thanks so much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply