Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Turning raw data into a grid layout Expand / Collapse
Author
Message
Posted Friday, January 4, 2013 3:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
Bhuvnesh (1/4/2013)
dwain.c (1/1/2013)

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

dwain , can you please explain above sql code , i am finding it hard to understand


The easiest would be for you to read the first article linked into my signature. It compares various examples to UNPIVOT.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1402768
Posted Friday, January 4, 2013 3:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 23,341, Visits: 32,075
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



You do know that your code is also sensitive to changes in DATEFIRST, correct?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1402776
Posted Friday, January 4, 2013 5:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 15, Visits: 471
Bhuvnesh,

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


is similar to

CROSS APPLY ( 
select High as val , 'High' as Type , DATEPART(dw, CAST(DateID AS CHAR(8))) as Weekday
union all
select Low as val, 'Low' as Type , DATEPART(dw, CAST(DateID AS CHAR(8)))
) a
GROUP BY CompanyName, Type


Hope you understood..

Thanks,
Saurabh
Post #1402830
Posted Friday, January 4, 2013 5:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
Saurv (1/4/2013)
Bhuvnesh,

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


is similar to

CROSS APPLY ( 
select High as val , 'High' as Type , DATEPART(dw, CAST(DateID AS CHAR(8))) as Weekday
union all
select Low as val, 'Low' as Type , DATEPART(dw, CAST(DateID AS CHAR(8)))
) a
GROUP BY CompanyName, Type


Hope you understood..

Thanks,
Saurabh


I believe that CAV offers a higher degree of potential parallelization. Read the discussion thread on the article for more information on that.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1403170
Posted Monday, May 5, 2014 9:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:45 PM
Points: 14, Visits: 18
Sounds like quite a complicated question to me. However, I am quite interested on this grid layout problem. I've had some experience on the winforms grid control, however I am quite new to this SQL environment, this question relieved my curiosity.
Post #1567765
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse