Can I use PIVOT / Cross Tab for this? If not...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can I use PIVOT / Cross Tab for this? If not...?

Author

Topic

jnghh
Starting Member

10 Posts

Posted - 2008-05-08 : 04:49:27

I have the following data structure (simplified)


declare @log table (
		date smalldatetime,
		category char(3),
		value1 int,
		value2 int)

insert into @log(date, category, value1, value2)
select '2008-01-01', 'ABC', 11,12 union all
select '2008-01-02', 'ABC', 35,53 union all
select '2008-01-03', 'ABC', 38,62 union all
select '2008-01-05', 'ABC', 59,95 union all
select '2008-01-02', 'XYZ', 42,21 union all
select '2008-01-04', 'XYZ', 9,7 union all
select '2008-01-05', 'XYZ', 89,45 union all
select '2008-01-01', 'HHH', 70,52 union all
select '2008-01-03', 'HHH', 3,83 union all
select '2008-01-05', 'HHH', 26,77

where
1) date is always up to the day (no time variation)
2) date and category can be considered a composite unique key

Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:


date       abc_value1  abc_value2  xyz_value1  xyz_value2  hhh_value1  hhh_value2
---------- ----------- ----------- ----------- ----------- ----------- -----------
01/01/2008 11          12          NULL        NULL        70          52
01/02/2008 35          53          42          21          NULL        NULL
01/03/2008 38          62          NULL        NULL        3           83
01/04/2008 NULL        NULL        9           7           NULL        NULL
01/05/2008 59          95          89          45          26          77

Ideally, the results include
- every day in the date range (even if there is no corresponding data for that date)
- the columns values to be dependent on the categories found within the date range

I came up with this


-- to fulfill requirement "every day in the date range"
declare @dt table (d smalldatetime)
insert into @dt
select '2008-01-01' union all
select '2008-01-02' union all
select '2008-01-03' union all
select '2008-01-04' union all
select '2008-01-05' 

-- to fulfill display all the categories (manually determined) 
select convert(varchar(10),d,101) as date, 
		abc.value1 as abc_value1, abc.value2 as abc_value2,
		xyz.value1 as xyz_value1, xyz.value2 as xyz_value2,
		hhh.value1 as hhh_value1, hhh.value2 as hhh_value2
from @dt dt
	left join @log abc on dt.d = abc.date and abc.category = 'ABC'
	left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ'
	left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'

just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.

Any ideas? Thoughts? Suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 04:59:16

I think you can achieve this using PIVOt but you might need to use it dynamically using D SQL as at any time categories and daterange are dynamic.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 05:03:44

Are there other categories than ABC, XYZ and HHH?

E 12°55'05.25"
N 56°04'39.16"

jnghh
Starting Member

10 Posts

Posted - 2008-05-08 : 21:56:46

quote:
Originally posted by Peso

Are there other categories than ABC, XYZ and HHH?

Very likely-- the categories are user-entered values, and once entered and "activated", the system may start tracking log entries for that category.

The only way to know what categories there are is based on a given date range, to select distinct category within that range.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources