Solved

# Converting Vertical Data to Horizontal Data through Aggregation

Posted on 2004-04-20
Medium Priority
716 Views
The following query does a good job of converting my rows to columns.  It works because There can only be either zero or one records for a combination of Va.VDate and Va.SOSID.  These two fields make my primary key.  The problem I am having is that reporting zero when a record doesn't exist is not acceptable.  I somehow need this query to return NULL if no record exists for a given date and sosid combination.  I also can't assume that if the query returns zero the record doesn't exist because there could be a record with units value of zero.

Create Function dbo.SOSCurrentVary(@StartDate DateTime)
Returns Table
AS
Return
Select Va.SOSID,
Max(Case When Va.VDate = @StartDate Then IsNull(Units,0) Else 0 End) CurUnits1,
Max(Case When Va.VDate = @StartDate+1 Then IsNull(Units,0) Else 0 End) CurUnits2,
Max(Case When Va.VDate = @StartDate+2 Then IsNull(Units,0) Else 0 End) CurUnits3,
Max(Case When Va.VDate = @StartDate+3 Then IsNull(Units,0) Else 0 End) CurUnits4,
Max(Case When Va.VDate = @StartDate+4 Then IsNull(Units,0) Else 0 End) CurUnits5,
Max(Case When Va.VDate = @StartDate+5 Then IsNull(Units,0) Else 0 End) CurUnits6,
Max(Case When Va.VDate = @StartDate+6 Then IsNull(Units,0) Else 0 End) CurUnits7
From SOSVary Va
Group By Va.SOSID

)
Go

The most important consideration here is efficiency.  It is not an option to make 7 joins or use 7 exists statements (which will result in 7 joins anyway)

I'm going to post an additional question asking how I can make this same query perform a clustered index seek as opposed to a clustered index scan.  My clustered index is made up of my primary key fields (SOSID, Vdate).

Billy
0
Question by:billy21
• 2
• 2

LVL 6

Author Comment

ID: 10867633
Table strucure is as follows:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SOSVary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SOSVary]
GO

CREATE TABLE [dbo].[SOSVary] (
[SOSID] [int] NOT NULL ,
[VDate] [datetime] NOT NULL ,
[Units] [smallmoney] NOT NULL ,
[VariationReason] [int] NOT NULL ,
[VariationNote] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VariedBySPNo] [int] NOT NULL ,
[VariedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SOSVary] WITH NOCHECK ADD
CONSTRAINT [PK_SOSVary] PRIMARY KEY  CLUSTERED
(
[SOSID],
[VDate]
) WITH  FILLFACTOR = 60  ON [PRIMARY]
GO

CONSTRAINT [DF__SOSVary__Units__52DAC1AA] DEFAULT (0) FOR [Units],
CONSTRAINT [DF__SOSVary__Variati__53CEE5E3] DEFAULT (0) FOR [VariationReason],
CONSTRAINT [DF__SOSVary__VariedD__54C30A1C] DEFAULT (getdate()) FOR [VariedDate]
GO

0

LVL 26

Accepted Solution

Hilaire earned 2000 total points
ID: 10867741

Create Function dbo.SOSCurrentVary(@StartDate DateTime)
Returns Table
AS
Return
Select Va.SOSID,
Max(Case When Va.VDate = @StartDate Then Units End) CurUnits1,
Max(Case When Va.VDate = @StartDate+1 Then Units End) CurUnits2,
Max(Case When Va.VDate = @StartDate+2 Then Units End)  CurUnits3,
Max(Case When Va.VDate = @StartDate+3 Then Units End)  CurUnits4,
Max(Case When Va.VDate = @StartDate+4 Then Units End)  CurUnits5,
Max(Case When Va.VDate = @StartDate+5 Then Units End)  CurUnits6,
Max(Case When Va.VDate = @StartDate+6 Then Units End)  CurUnits7
From SOSVary Va
Group By Va.SOSID
)
0

LVL 6

Author Comment

ID: 10867846
Thanks Hilare you're a champion!
0

LVL 26

Expert Comment

ID: 10867864
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month15 days, 19 hours left to enroll