Turning raw data into a grid layout

CREATE TABLE [dbo].[TestGrid]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](200) NOT NULL,
[DateID] [int] NOT NULL,
[High] [float] NOT NULL,
[Low] [float] NOT NULL
)

INSERT INTO [dbo].[TestGrid] (CompanyName,DateID,High,Low)
VALUES ('ABC',20121201,0.5,1.0)

INSERT INTO [dbo].[TestGrid] (CompanyName,DateID,High,Low)
VALUES ('ABC',20121202,0.6,1.5)

INSERT INTO [dbo].[TestGrid] (CompanyName,DateID,High,Low)
VALUES ('ABC',20121203,1.0,1.6)

INSERT INTO [dbo].[TestGrid] (CompanyName,DateID,High,Low)
VALUES ('XYZ',20121201,0.5,0.4)

INSERT INTO [dbo].[TestGrid] (CompanyName,DateID,High,Low)
VALUES ('XYZ',20121202,0.6,0.5)

INSERT INTO [dbo].[TestGrid] (CompanyName,DateID,High,Low)
VALUES ('XYZ',20121203,1.0,0.6)

ID CompanyName DateID High Low
----------- ----------------------------------------------------------
1 ABC 20121201 0.5 0.1
2 ABC 20121202 0.6 0.5
3 ABC 20121203 1 0.6
4 XYZ 20121201 0.5 0.4
5 XYZ 20121202 0.6 0.5
6 XYZ 20121203 1 0.6

SELECT CompanyName, Type
,Sunday=MAX(CASE WHEN weekday = 1 THEN Val END)
,Monday=MAX(CASE WHEN weekday = 2 THEN Val END)
,Tuesday=MAX(CASE WHEN weekday = 3 THEN Val END)
,Wednesday=MAX(CASE WHEN weekday = 4 THEN Val END)
,Thursday=MAX(CASE WHEN weekday = 5 THEN Val END)
,Friday=MAX(CASE WHEN weekday = 6 THEN Val END)
,Saturday=MAX(CASE WHEN weekday = 7 THEN Val END)
FROM dbo.TestGrid
CROSS APPLY (
VALUES(High, 'High', DATEPART(dw, CAST(DateID AS CHAR(8))))
,(Low, 'Low', DATEPART(dw, CAST(DateID AS CHAR(8))))) a(Val, Type, weekday)
GROUP BY CompanyName, Type

SELECT
CompanyName
,Type
,Sunday = MAX(CASE WHEN weekday = datename(dw, dateadd(dd, 1 - 2, '19000101')) THEN Val END)
,Monday = MAX(CASE WHEN weekday = datename(dw, dateadd(dd, 2 - 2, '19000101')) THEN Val END)
,Tuesday = MAX(CASE WHEN weekday = datename(dw, dateadd(dd, 3 - 2, '19000101')) THEN Val END)
,Wednesday = MAX(CASE WHEN weekday = datename(dw, dateadd(dd, 4 - 2, '19000101')) THEN Val END)
,Thursday = MAX(CASE WHEN weekday = datename(dw, dateadd(dd, 5 - 2, '19000101')) THEN Val END)
,Friday = MAX(CASE WHEN weekday = datename(dw, dateadd(dd, 6 - 2, '19000101')) THEN Val END)
,Saturday = MAX(CASE WHEN weekday = datename(dw, dateadd(dd, 7 - 2, '19000101')) THEN Val END)
FROM dbo.TestGrid
CROSS APPLY (
VALUES(High, 'High', DATENAME(dw, CAST(DateID AS CHAR(8))))
,(Low, 'Low', DATENAME(dw, CAST(DateID AS CHAR(8))))) a(Val, Type, weekday)
GROUP BY CompanyName, Type;

dwain.c (1/2/2013)

Lynn Pettis (1/2/2013)

dwain.c (1/2/2013)

Tava (1/2/2013)

To answer the question about why is DateID int and not datetime its an old table and data already in there. I assume its for an indexing/performance benefit and everything is based on dateid for searching? does that sound about right?

It is not the first time I've heard of someone being stuck with a legacy design. If you're in SQL 2008 and designing anew, using DATE datatype would provide the same indexing/performance benefit as what you've done. I actually doubt there would be a difference had the field been created as a DATETIME (just perhaps a little more application housekeeping to ensure that no time part is stored with it).

Lynn - Nice way to eliminate the dependency on DATEFIRST.

I don't think it works the way I expected it to, looks like it is missing part of the code due to a poor copy and past. I'll have to look at later when I get back to the apartment.

Hehe. I didn't try to run it but understood what you were trying to do. I think you meant this (in the CASE statements):

SELECT datename(dw, dateadd(day, 1 - 2, '19000101'))  -- for Sunday

SELECT CompanyName, 'Low' as Type,
[Sunday], [Monday], [tuesday], [wednesday], [thursday],[Friday],[Saturday]
FROM
(
select
CompanyName,
Low,
CASE
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 1 THEN 'Sunday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 2 THEN 'Monday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 3 THEN 'tuesday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 4 THEN 'wednesday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 5 THEN 'thursday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 6 THEN 'Friday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 7 THEN 'Saturday'
END as WeekDays
from [TestGrid]
) AS SourceTable
PIVOT
(
SUM(Low)
FOR WeekDays IN ([Sunday], [Monday], [tuesday], [wednesday], [thursday],[Friday],[Saturday])
) AS PivotTable
UNION
SELECT CompanyName, 'High' as Type,
[Sunday], [Monday], [tuesday], [wednesday], [thursday],[Friday],[Saturday]
FROM
(
select
CompanyName,
High,
CASE
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 1 THEN 'Sunday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 2 THEN 'Monday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 3 THEN 'tuesday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 4 THEN 'wednesday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 5 THEN 'thursday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 6 THEN 'Friday'
WHEN DATEPART(dw, CAST(DateID AS CHAR(8))) = 7 THEN 'Saturday'
END as WeekDays
from [TestGrid]
) AS SourceTable
PIVOT
(
SUM(High)
FOR WeekDays IN ([Sunday], [Monday], [tuesday], [wednesday], [thursday],[Friday],[Saturday])
) AS PivotTable