t-sql question, help please!! - 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

SQL Server 2005 Forums

Transact-SQL (2005)

t-sql question, help please!!

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2010-03-10 : 17:47:36

I want to take the column values and make it comma separated by enclosing in the [], below is the expected
output:
[Order No_],[PO No_],[Cust_ No_],[Vend_ No_]

Please help.

Here is the sample data:
Create table #Temp
(col1 varchar(200))

Insert into #Temp
Select 'Order No_'
union all
Select 'PO No_'
union all
Select 'Cust_ No_'
union all
Select 'Vend_ No_'

select * from #Temp

below is the expected output:
[Order No_],[PO No_],[Cust_ No_],[Vend_ No_]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 17:55:28

[code]
declare @str varchar(4000)
select @str = isnull(@str + ',', '') + quotename(col1)
from #Temp
[/code]

KH
[spoiler]Time is always against us[/spoiler]

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-10 : 18:54:41

quote:
Originally posted by khtan

declare @str varchar(4000)
select @str = isnull(@str + ',', '') + quotename(col1)
from #Temp

KH
[spoiler]Time is always against us[/spoiler]

While that will work, it's not guaranteed to return the columns in the correct order. For that, do something like this:

Create table #Temp
(col1 varchar(200), col2 varchar(200), col3 varchar(200), col4 varchar(200))

Insert into #Temp
Select '[Order No_]', '[PO No_]', '[Cust_ No_]', '[Vend_ No_]'

select col1 + ', ' + col2 + ', ' + col3 + ', ' + col4 + ', ' from #Temp

There are 10 types of people in the world, those that understand binary, and those that don't.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 19:48:26

[code]While that will work, it's not guaranteed to return the columns in the correct order[/code]
then add a ORDER BY clause

[code]
declare @str varchar(4000)
select @str = isnull(@str + ',', '') + quotename(col1)
from #Temp
order by col1
[/code]

quote:
For that, do something like this: . . .

in OP's table the cols is in rows. How are you going to transpose it to column wise ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources