SQLTeam.com | Weblogs | Forums

Change rows to columns

sql2012

#1

Hi,
Can we switch the tables rows to columns like this?using sql server 2012

Declare @mySpecTable table(prodID int,prodName varchar(30),specName varchar(100), specValue varchar(100))

Insert into @mySpecTable values
(1,'Sink1','Shape','Round')
,(1,'Sink1','Color','White')
,(1,'Sink1','Style','Pedestal')

,(2,'Sink2','Shape','Square')
,(2,'Sink2','Color','Black')
,(2,'Sink2','Style','Wallmount')

,(3,'Sink3','Shape','Round')
,(3,'Sink3','Color','White')
,(3,'Sink3','Style','Pedestal')

prodID prodName specName specValue
1	Sink1	 Shape	Round
1	Sink1	Color	White
1	Sink1	Style	Pedestal
2	Sink2	Shape	Square
2	Sink2	Color	Black
2	Sink2	Style	Wallmount
3	Sink3	Shape	Round
3	Sink3	Color	White
3	Sink3	Style	Pedestal

Required display

ProductName   Shape    Color   Style
Sink1         Round   White     Pedestal
Sink2         Square  Black    Wallmount
Sink3         Round   White      Pedestal

#2
SELECT
	prodName,
	MAX(CASE WHEN specName = 'Shape' THEN specValue END ) AS [Shape],
	MAX(CASE WHEN specName = 'Color' THEN specValue END ) AS [Color],
	MAX(CASE WHEN specName = 'Style' THEN specValue END ) AS [Style]
FROM
	@mySpecTable
GROUP BY
	prodName;

#3

If you're using sql2012, you can use the PIVOT function as well.

SELECT prodName, [Shape],[Color],[Style] FROM @mySpecTable
  PIVOT(Max(specValue)FOR specName IN( [Shape],[Color],[Style] )) AS PVT 


#4

Thanks @scarela and @JamesK