pivot with ctes

0

Please login or register to vote for this query.

(click on this box to dismiss)

Psychology & Neuroscience Meta

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

;with productionline(id, productionline) as
(
  select 1, 'Line A' union all
  select 2, 'Line B'
),
productiondata (id, productionline_id, unitsproduced, productiondate) as
(
  select 1, 1, 200, '2014-09-18' union all
  select 2, 2, 50, '2014-09-18' union all
  select 3, 1, 100, '2014-09-19'
),
cte as
( 
  select pl.ProductionLine as ProductionLine, 
		pd.ProductionDate as ProductionDate, 
		pd.UnitsProduced as UnitsProduced 
  from ProductionLine pl
  join ProductionData pd 
	on pl.ID = pd.ProductionLine_ID
)
select *
from
(
  select ProductionLine, 
    ProductionDate, 
    UnitsProduced
    from  cte
) x
pivot
(
    max(UnitsProduced) 
    for ProductionLine in ([Line A])
) pvt

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results