Please help me on data selection

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please help me on data selection

Author

Topic

pacolim
Starting Member

11 Posts

Posted - 2010-02-03 : 04:02:29

I have the first table, is there any way to select from there and generate the second table output?

[url=http://www.imagechicken.com]

[/url]

Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 04:18:27

yup. see below


DECLARE @List varchar(3000)

SELECT @List = COALESCE(@List + '],[','') + Val
FROM (SELECT A as Val FROM Table
      UNION
      SELECT B FROM Table
      UNION
      SELECT C FROM Table
     )t

SET @SQL='SELECT *
FROM 
(
SELECT Key,Cat,Val
FROM Table t
UNPIVOT ( Val FOR Cat IN ([A],[B],[C]))u
)m
PIVOT (MAX(Cat) FOR Val IN ([' + @List + '])p'

EXEC (@Sql)