sql - learn pivoting

sql.sql

--;with q as (
--	select id, color, intensity
--	from (
--		values
--			 (1,'blue',4)
--			,(2,'red',5)
--			,(3,'blue',7)
--			,(4,'blue',1)
--			,(5,'yellow',3)
--	) colors (id, color, intensity)
--)
--			--select * from q

--select *
--from (
--	select id, color, intensity
--	from q
--)	y

--pivot (
--	sum(intensity)
--	for color in ([blue], [red], [yellow])
--) pt

-----------------------

select color, sum(intensity)
from (
	values
		 (1,'blue',4)
		,(2,'red',5)
		,(3,'blue',7)
		,(4,'blue',1)
		,(5,'yellow',3)
) t (id, color, intensity)
group by color



;with colors as (
	select color, intensity
	from (
		values
			 (1,'blue',4)
			,(2,'red',5)
			,(3,'blue',7)
			,(4,'blue',1)
			,(5,'yellow',3)
	) t (id, color, intensity)
)
select		*
from		colors
pivot (
	--count(id)
	sum(intensity)
	for color in (
		 [blue]
		,[red]
		,[yellow]
		,[black]
	)
) pt

/*
color  
------ -----------
blue   12
red    5
yellow 3

(3 row(s) affected)

blue        red         yellow      black
----------- ----------- ----------- -----------
12          5           3           NULL

(1 row(s) affected)
*/