• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

Converting Vertical Data to Horizontal Data through Aggregation

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

Thanks in advance,

Billy
0
billy21
Asked:
billy21
  • 2
  • 2
1 Solution
 
billy21Author Commented:
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

ALTER TABLE [dbo].[SOSVary] ADD
      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
 
HilaireCommented:
what about

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
 
billy21Author Commented:
Thanks Hilare you're a champion!
0
 
HilaireCommented:
Glad to help and thks for the grade !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now