done some research on this but haven't managed to find an example that works for me.
I have a table, image shown below, fiddle here
what I want to do is convert the rows into columns. I read un on pivots but am lost a little.
I want my output to be:
using sql, how can I achieve this.
Does this have to be achieved with variables?
You can use the PIVOT function to get the result. If you have a known number of values, then you can hard-code the query similar to the following:
select type, color,
[1], [1.5], [2], [3], [4]
from
(
select type, color, length, price
from items
) d
pivot
(
max(price)
for length in ([1], [1.5], [2], [3], [4])
) piv
order by type;
See SQL Fiddle with Demo. But if you are going to have an unknown number of length values, then you will need to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(length)
from items
group by length
order by length
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT type, color, ' + @cols + '
from
(
select type, color, length, price
from items
) x
pivot
(
max(price)
for length in (' + @cols + ')
) p
order by type'
execute sp_executesql @query;
Here is a query to PIVOT this table. But you should know Length values to define rows if you don't know them then you need to use dynamic query.
SELECT Type,Color,
MAX(CASE WHEN Length=1 THEN price END) as [1],
MAX(CASE WHEN Length=1.5 THEN price END) as [1.5],
MAX(CASE WHEN Length=2 THEN price END) as [2],
MAX(CASE WHEN Length=3 THEN price END) as [3],
MAX(CASE WHEN Length=4 THEN price END) as [4]
FROM items
GROUP BY Type,Color
