There are several ways that you can transform this data. In your original post, you stated that PIVOTseems too complex for this scenario, but it can be applied very easily using both the UNPIVOT andPIVOT functions in SQL Server.
However, if you do not have access to those functions this can be replicated using UNION ALL toUNPIVOT and then an aggregate function with a CASE statement to PIVOT:
sum(casewhen color ='Red'then value else0end) Red,
sum(casewhen color ='Green'then value else0end) Green,
sum(casewhen color ='Blue'then value else0end) Blue
from(select color, Paul value,'Paul' name
unionallselect color, John value,'John' name
unionallselect color, Tim value,'Tim' name
unionallselect color, Eric value,'Eric' name
The UNION ALL performs the UNPIVOT of the data by transforming the columns Paul, John, Tim, Eric into separate rows. Then you apply the aggregate function sum() with the case statement to get the new columns for each color.
Unpivot and Pivot Static Version:
Both the UNPIVOT and PIVOT functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:
select name,[Red],[Green],[Blue]from(select color, name, value
value for name in(Paul, John, Tim, Eric)) unpiv
sum(value)for color in([Red],[Green],[Blue])) piv
The inner query with the UNPIVOT performs the same function as the UNION ALL. It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.
Dynamic Pivot Version:
If you have an unknown number of columns (Paul, John, Tim, Eric in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT and then PIVOT:
DECLARE@colsUnpivot AS NVARCHAR(MAX),@query AS NVARCHAR(MAX),@colsPivot as NVARCHAR(MAX)select@colsUnpivot = stuff((select','+quotename(C.name)from sys.columns as C
where C.object_id = object_id('yourtable')and
C.name <>'color'forxml path('')),1,1,'')select@colsPivot = STUFF((SELECT','+ quotename(color)from yourtable t
FORXML PATH(''), TYPE
='select name, '+@colsPivot+'
select color, name, value
value for name in ('+@colsUnpivot+')
for color in ('+@colsPivot+')
The dynamic version queries both yourtable and then the sys.columns table to generate the list of items to UNPIVOT and PIVOT. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors and/or names this will generate the list at run-time.
All three queries will produce the same result:
| NAME | RED | GREEN | BLUE |-----------------------------| Eric |3|5|1|| John |5|4|2|| Paul |1|8|2|| Tim |1|3|9|