Pivoting Data in SQL

Question

What is proper syntax to convert from Table Table2Dimension to Table DesiredTable in MSSQL?

Answer 1

Since you are using SQL Server you can get the result by using the PIVOT function:

select [row], [1], [2], [3]
from
(
  select [row], col, value
  from Table2Dimension
) d
pivot
(
  max(value)
  for col in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. This gives the result:

| ROW | 1 | 2 | 3 |
|-----|---|---|---|
|   1 | 1 | 2 | 3 |
|   2 | 4 | 5 | 6 |
|   3 | 7 | 8 | 9 |

Answer 2

select row,
       sum(case when col = 1 then value end) as [1],
       sum(case when col = 2 then value end) as [2],
       sum(case when col = 3 then value end) as [3]
from your_table
group by row