Concate data in single line - 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 2008 Forums

Transact-SQL (2008)

Concate data in single line

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-03-10 : 04:59:12

Hi All,

I have data coming in each row like

BaseUnit:5
Monitor:6
Racks:14
Servers:14
Speakers:8
Printer:4
SmallServer:5

I want that data in single line in SQL but with some different way..
LIKE

BaseUnit:5,Monitor:6|Racks:14,Servers:14|Speakers:8,Printer:4|SmallServer:5

Join character between each row will be alternative comma (,) then pipe (|).

Please help

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 05:17:26

Select 'BaseUnit:'+Col1+',Monitor:'+Col2+'|Racks:'+Col3
From tableName

Note that if any of the column is of non-Character Datatype then you will need to Cast that column to Varchar. e.g. if Col1 is Non-Character then the query should be as following

Select 'BaseUnit:'+cast(Col1 as varchar(10))+',Monitor:'+Col2+'|Racks:'+Col3
From tableName

Cheers
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 05:56:59

[code]
declare @str varchar(max)

with tbl as
(
select col, row_no = row_number() over (order by col)
from yourtbl
)
select @str = isnull(@str + case when row_no % 2 = 0 then ',' else '|' end, '') + col
from tbl
order by row_no

print @str
[/code]

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

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-03-10 : 07:34:54

Thank you khtan. its done with that way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources