Bhuvnesh (1/3/2013)

---

i tried it with PIVOT but its more resource intensive (as compared to other provided solution ) . can we avoid UNION here

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