UNPIVOT - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

UNPIVOT

Author

Topic

Derick
Starting Member

17 Posts

Posted - 2009-07-15 : 09:49:04

Hi All,
What am I doing wrong?

SELECT ID, Course, req
FROM dbo.SF_LearnDesignation
UNPIVOT(req FOR Course IN(select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'SF_LearnDesignation' and column_name like 'DoCourse%')) AS U

I need to read the columns of table SF_LearnDesignation dynamicaly, also the value of each col is 1 or 0, I need to filter only the 1

Thanks

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

Derick
Starting Member

17 Posts

Posted - 2009-07-15 : 10:28:55

Still does not work...

Declare @col VarChar(Max)
Declare @colnames as varchar(max)

Set @col = ''
Select @col = @col + Column_Name + ', '
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = 'SF_LearnDesignation'
And Column_Name Like 'DoCourse%'
order by ordinal_position

set @colnames = left(@col,len(@col)-2)

print @colnames

SELECT ID, Course, req
FROM dbo.SF_LearnDesignation
UNPIVOT(req FOR Course IN(@colnames)) AS U

Error = Incorrect syntax near '@colnames'

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-15 : 11:22:43

declare @sql varchar(8000)
set @sql='
SELECT ID, Course, req
FROM dbo.SF_LearnDesignation
UNPIVOT(req FOR Course IN('+@colnames+')) AS U'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Derick
Starting Member

17 Posts

Posted - 2009-07-15 : 11:32:11

Thanks, one of the columns do not have a value of 1 but only 0, now I get the error on that col:
The type of column "DoCourse15Mod142" conflicts with the type of other columns specified in the UNPIVOT list.

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources