Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server Temporary Tables, CTE, "Dummy" Table, Pivot - simple example
;WITH CTEearthquakes as (
SELECT
earthquakes.cDate,
earthquakes.Magnitude
FROM (
VALUES
('1/5/2007', 5.8),
('1/1/2008', 5.6),
('1/1/2009', 5.8),
('1/2/2010', 6.1),
('1/1/2011', 7),
('1/1/2012', 6.8),
('1/3/2013', 5.6),
('1/1/2014', 6.5),
('1/2/2015', 5.5),
('12/30/2016', 5.5)
) as earthquakes(cDate, Magnitude)
), convertedE as (
SELECT
convert(DATE, CTEearthquakes.cDate) as fDate,
CTEearthquakes.Magnitude
FROM
CTEearthquakes
), GroupedE as (
SELECT
YEAR(convertedE.fDate) as gDate,
AVG(convertedE.Magnitude) as Magnitude
FROM
convertedE
GROUP BY
YEAR(convertedE.fDate)
), StageE as (
SELECT
'Magnitude' as aType,
CASE
WHEN GroupedE.gDate <2010 THEN 'STAGE 1'
WHEN GroupedE.gDate <2014 THEN 'STAGE 2'
ELSE 'STAGE 3'
END as Stage,
GroupedE.Magnitude
FROM GroupedE)
-- select * from StageE
SELECT
*,
(PivotEarthquakes.[STAGE 1] + PivotEarthquakes.[STAGE 2] + PivotEarthquakes.[STAGE 3])/3 AVGstages
FROM
StageE
PIVOT (
AVG(StageE.Magnitude)
FOR StageE.Stage IN ([STAGE 1], [STAGE 2], [STAGE 3])
) as PivotEarthquakes
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment