May 21, 2009 at 10:23 am
I have a requirement to provide a set of data in table format. The challenge is that the data is resident in the db in a format that requires it to be summed and pivoted to get the desired output format. This script to create and load the table with test records is:
CREATE TABLE [dbo].[tblEventQuantity](
[EventQuantityId] [int] IDENTITY(1,1) NOT NULL,
[EventId] [int] NOT NULL,
[Year] [smallint] NOT NULL DEFAULT (NULL),
[ProcurementQuantity] [int] NOT NULL DEFAULT ((0)),
[ProductionQuantity] [int] NOT NULL DEFAULT ((0)),
[FieldingQuantity] [int] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_tblEventQuantity] PRIMARY KEY CLUSTERED
(
[EventQuantityId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- Load test data
DELETE FROM dbo.tblEventQuantity
INSERT INTO dbo.tblEventQuantity
(EventId, Year, ProcurementQuantity, ProductionQuantity, FieldingQuantity)
VALUES (30, 2009, 12, 23, 2)
INSERT INTO dbo.tblEventQuantity
(EventId, Year, ProcurementQuantity, ProductionQuantity, FieldingQuantity)
VALUES (34, 2005, 54, 544, 5)
INSERT INTO dbo.tblEventQuantity
(EventId, Year, ProcurementQuantity, ProductionQuantity, FieldingQuantity)
VALUES (35, 2005, 32, 2, 45)
The query needs to provide results like: (See the attachment for better formatting)
2005 2006 2007 2008 2009
Production 546 0 0 0 23
Fielding 50 0 0 0 2
Procurement 86 0 0 0 12
As you can see, the db stores a record for each eventId and years can duplicate. The stored procedure needs to accept a begin year and an end year and produce output as above. The years between 2005 and 2009 that are NOT in the dataset need to be built dynamically and the quantities need to be set to 0.
Thanks in advance for your assistance.
May 21, 2009 at 1:37 pm
Have a look at the following Article from Jeff Moden. He discusses dynamic cross tabs in detail.
http://www.sqlservercentral.com/articles/cross+tab/65048/
-Luke.
May 21, 2009 at 1:57 pm
I did some work on a dynamic pivot in a recent thread here http://www.sqlservercentral.com/Forums/Topic713483-338-1.aspx
I think you'd need to incorporate a tally table to get the missing years somehow
DECLARE @MINYR INT, @MAXYR INT
SELECT @MINYR = MIN([YEAR]),
@MAXYR = MAX([YEAR])
FROM tblEventQuantity ;
SELECT dates.[Year]
FROM tblEventQuantity AS EQ
RIGHT OUTER JOIN
(
SELECT T.N-1 + @MINYR AS [Year]
FROM dbo.Tally AS T
WHERE T.N-1 + @MINYR < @MAXYR
) dates
ON EQ.[Year] = dates.[Year]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply