How to set the dynamic column name in Unpivot SQL

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

SSIS and Import/Export (2005)

How to set the dynamic column name in Unpivot SQL

Author

Topic

sharu_tan
Starting Member

11 Posts

Posted - 2008-04-10 : 03:01:17

Dear All,

We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:

declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

The above query while executing geting error message "Incorrect syntax near '@aw'"

please can anybody solve this issue.

Thanks,
Syed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 05:32:45

Books Online says all columns in the IN part for UNPIVOT should have [ ] around it.

declare @aw Varchar(100)
set @aw = '[1990],[1991]'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-10 : 05:43:34

quote:
Originally posted by Peso

Books Online says all columns in the IN part for UNPIVOT should have [ ] around it.

declare @aw Varchar(100)
set @aw = '[1990],[1991]'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

E 12°55'05.25"
N 56°04'39.16"

Dont you need dynamic sql in this case?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 06:06:41

See this: http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sharu_tan
Starting Member

11 Posts

Posted - 2008-04-10 : 08:37:38

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 08:46:53

[code]declare @aw Varchar(100)
set @aw = '1990,1991'

declare @sql varchar(max)

SET @SQL = '
SELECT unpvt.name,
unpvt.year,
unpvt.data
FROM (
SELECT *
FROM t1
where name <> 'name'
) AS p
UNPIVOT (
data
FOR year IN ([' + REPLACE(@aw, ',', '],[' + '])
) AS unpvt'

exec (@sql)[/code]

E 12°55'05.25"
N 56°04'39.16"

sharu_tan
Starting Member

11 Posts

Posted - 2008-04-11 : 02:13:52

Thanks a lot its workig fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources