In SQL Server 2005+ you can use the PIVOT function to perform this. A pivot takes your values from rows and converts it into columns.
There are a few ways that you can pivot the data. If you know all of the values ahead of time, then you can hard-code you query. Otherwise you could use dynamic SQL to generate the query at run-time.
A static version of a pivot would be similar to this:
select *
from
(
select col1, col2
from table1
) src
pivot
(
max(col1)
for col2 in (test, blah, value)
) piv
See SQL Fiddle with Demo.
If you have an unknown number of values, then you would generate dynamic SQL similar to this:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(col2)
from table1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + ' from
(
select col1, col2
from table1
) x
pivot
(
max(col1)
for col2 in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo
These are taking the col2 row values from your table and turning them into columns. The result for both is the same:
| TEST | BLAH | VALUE |
-----------------------
| 1 | 2 | 3 |
Prior to SQL Server 2005 or in databases that do not have a pivot function, then you would use an aggregate function with a case expression to transform the data:
select
max(case when col2 = 'test' then col1 end) test,
max(case when col2 = 'blah' then col1 end) blah,
max(case when col2 = 'value' then col1 end) value
from table1
See SQL Fiddle with Demo
One another way to do this if you want to repeat the columns, over and over again is to use the UNPIVOT and PIVOT function together to get the result. If you have the sample data:
CREATE TABLE yourtable
([id] int, [name] varchar(10), [type] varchar(10))
;
INSERT INTO yourtable
([id], [name], [type])
VALUES
(1, 'John', 'dog'),
(2, 'Tim', 'bird'),
(3, 'Betty', 'cat'),
(4, 'Jim', 'rat')
;
And you want to repeat the id, name and type values in repeating columns, then you can use:
select *
from
(
select
col +'_'+cast(rn as varchar(50)) col ,
value
from
(
select
cast(id as varchar(10)) id,
name,
type,
row_number() over(order by id) rn
from yourtable
) src
unpivot
(
value
for col in (id, name, type)
) unpiv
) src
pivot
(
max(value)
for col in (id_1, name_1, type_1,
id_2, name_2, type_2,
id_3, name_3, type_3,
id_4, name_4, type_4)
) piv
See SQL Fiddle with Demo. The result of this will be:
| ID_1 | NAME_1 | TYPE_1 | ID_2 | NAME_2 | TYPE_2 | ID_3 | NAME_3 | TYPE_3 | ID_4 | NAME_4 | TYPE_4 |
-----------------------------------------------------------------------------------------------------
| 1 | John | dog | 2 | Tim | bird | 3 | Betty | cat | 4 | Jim | rat |
Edit #2, seeing your sample data you could use the following:
select *
from
(
select
col +'_'+cast(rn as varchar(50)) col ,
value
from
(
select
col1,
convert(varchar(10), col2, 120) col2,
convert(varchar(10), col3, 120) col3,
row_number() over(order by col1) rn
from yourtable
) src
unpivot
(
value
for col in (col1, col2, col3)
) unpiv
) src
pivot
(
max(value)
for col in (col1_1, col2_1, col3_1,
col1_2, col2_2, col3_2)
) piv
See SQL Fiddle with Demo
Gives the result:
| COL1_1 | COL2_1 | COL3_1 | COL1_2 | COL2_2 | COL3_2 |
-----------------------------------------------------------------------
| a | 2012-01-12 | 2012-01-13 | b | 2012-01-14 | 2012-01-14 |
