Concatenate Column Values from Multiple Rows into

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)

Concatenate Column Values from Multiple Rows into

Author

Topic

irfanshirur
Starting Member

21 Posts

Posted - 2009-07-13 : 05:49:01

Hello

I have a query which will retrive data like this :
(I am joining to 3 tables thats y iam getting for same ID multiple records with difrent Marks,)

ID Name Marks
1 ABC 50
1 ABC 60
1 ABC 70

But i want the output in sigle row with marks as comma seperated.

is it possible in SQL 2005 ?

Output should be

ID Name Marks
1 ABC 50,60,70

Please can any one help me out to solve this problem .

Thanks and regards
Mohammed Irfan

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-13 : 05:58:43

Yes it is possible,

select id,name,stuff(( select distinct ','+ marks from @temp where t.id = id and t.name = name for xml path('')),1,1,'') from @temp t

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-13 : 06:00:15

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

irfanshirur
Starting Member

21 Posts

Posted - 2009-07-13 : 06:40:54

Hello

SELECT DISTINCT s1.claim,
STUFF((SELECT DISTINCT ',' + s2.CPT_CD FROM dbo.Med_Procedures AS s2 WHERE s2.claim = s1.claim FOR XML PATH('Med_Procedures')), 1, 1, '') AS CODES
FROM dbo.Med_Procedures AS s1
where s1.claim='0000000019'

whats wrong in this query ? i am getting Error :Line 2: Incorrect syntax near 'XML'.

Claim column will be same for all rows but only the CPT_cd vl differ.

Please can you tell me what i did mistake in the query ?

thanks
Mohammed Irfan

irfanshirur
Starting Member

21 Posts

Posted - 2009-07-13 : 06:42:07

quote:
Originally posted by Nageswar9

Yes it is possible,

select id,name,stuff(( select distinct ','+ marks from @temp where t.id = id and t.name = name for xml path('')),1,1,'') from @temp t

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-07-13 : 06:57:11

CREATE FUNCTION FetchCol
(
@NAME VARCHAR(50),
@ID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=COALESCE(@SQL + ',','') + CAST(MARKS AS VARCHAR) FROM T
WHERE NAME=@NAME AND ID=@ID
RETURN @SQL
END

SELECT DISTINCT NAME,ID,DBO.FetchCol(NAME,ID) FROM T

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 07:51:34

quote:
Originally posted by irfanshirur

whats wrong in this query ? i am getting Error :Line 2: Incorrect syntax near 'XML'.

The FOR XML PATH needs an empty identifier, not 'med_xxxx'.

N 56°04'39.26"
E 12°55'05.63"

irfanshirur
Starting Member

21 Posts

Posted - 2009-07-13 : 08:59:03

quote:
Originally posted by Peso

quote:
Originally posted by irfanshirur

whats wrong in this query ? i am getting Error :Line 2: Incorrect syntax near 'XML'.
The FOR XML PATH needs an empty identifier, not 'med_xxxx'.

N 56°04'39.26"
E 12°55'05.63"

Hello

thanks for your all replies.....thanks a lots....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources