Query to concatenate values - 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)

Query to concatenate values

Author

Topic

jmiller121
Starting Member

8 Posts

Posted - 2010-06-07 : 21:51:38

I wasn't really sure how to word the subject, but here is what I want to do....

I have a table with 2 columns - CARDNUMBER and DESC

The values are laid out like this...
CN1, M121
CN1, E131
CN1, L121
CN2, L131
CN2, E132
CN3, M141
CN3, M151
CN3, E152

What I would like to do is write a query who's output has 3 rows (one for each of the CARDNUMBER values) and the second column would contain a concatenation of all of the DESC values for that particular card number (with dashes between the values). So the output for the table above would look like this:

CN1, M121-E131-L121
CN2, L131-E132
CN3, M141-M151-E152

Is something like this possible?

Thanks for your help!
JMiller

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-07 : 22:29:19

concatenate records without UDF

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

urzsuresh
Starting Member

30 Posts

Posted - 2010-06-08 : 01:06:12

Hello friend,
Kindly try below query . you will get solution.
Declare @t table
(
a varchar(10),
b varchar(10)
)

insert into @t
Select 'CN1','M121' UNION ALL
Select 'CN1','E131' UNION ALL
Select 'CN1','L121' UNION ALL
Select 'CN2','L131' UNION ALL
Select 'CN2','E132' UNION ALL
Select 'CN3','M141' UNION ALL
Select 'CN3','E151' UNION ALL
Select 'CN3','E152'

Select * from @t

Select a,t2.result from @t t1
cross apply
(
Select result=stuff((Select +'-'+ b from @t where t1.a=a for xml path('')),1,1,'')
)t2
group by t1.a,t2.result

Suri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources