Create columns name from rows?

Question

I have this data :

name      | coulmnnuber
Newyork   | 1
washington| 2
denmark   | 3
Holand    | 4

Data should look like this :

1           2           3          4
New york    Washington  denmark    Holand

Answer 1

You can use an aggregate function with a CASE expression to convert the rows of data into columns:

select 
  max(case when coulmnnuber = 1 then name end) [1],
  max(case when coulmnnuber = 2 then name end) [2],
  max(case when coulmnnuber = 3 then name end) [3],
  max(case when coulmnnuber = 4 then name end) [4]            
from yourtable;

See SQL Fiddle with Demo

Or you can use the PIVOT function:

select [1], [2], [3], [4]
from yourtable
pivot
(
  max(name)
  for coulmnnuber in ([1], [2], [3], [4])
) piv;

See SQL Fiddle with Demo