how to rotate or flip table.

[ravinder_k]

I have a key value pair as listed below.

Key value
------ ----------
name rob
age 30
sex male
... ...
... ...

would like to see a result set like below.

name age sex ...
---- --- ---- -----
rob 30 male ...

Note: the number of key value pairs are unknown.

[rmiao]

Take look at 'Cross-Tab Reports' in sql books online.

[MAK]

create table mykeyvalue ([Key] varchar(10), [value] varchar(100))
insert into mykeyvalue select 'Name','Rob'
insert into mykeyvalue select 'Age','30'
insert into mykeyvalue select 'Sex','Male'

insert into mykeyvalue select 'Name','Sam'
insert into mykeyvalue select 'Age','37'
insert into mykeyvalue select 'Sex','Male'

insert into mykeyvalue select 'Name','Eiko'
insert into mykeyvalue select 'Age','19'
insert into mykeyvalue select 'Sex','FeMale'

select identity(int,0,1) as ID,[key],[value] into mytable from mykeyvalue


SELECT [id]/3 as MYID,
max(CASE [Key] WHEN 'Name' THEN [Value] ELSE NULL END) AS [Name],
max(CASE [Key] WHEN 'Age' THEN [Value] ELSE NULL END) AS [Age],
max(CASE [Key] WHEN 'Sex' THEN [Value] ELSE NULL END) AS [Sex]
FROM mytable group by [id]/3

[ravinder_k]

thanks a lot... great solution...