Return Multiple rows in single row

[Gnism]

Hi all…I’m trying to turn this multi-row result into the single the row result shown below.

SELECT patID, txtCode FROM table1 WHERE patID IN(547385,1094870)
Produces:
patID txtCode
----------- -----------------
547385 001
547385 002
547385 01B
547385 026
1094870 001
1094870 002
1094870 007
1094870 009
1094870 015
1094870 016
1094870 017
1094870 018
1094870 020
1094870 025
1094870 026
1094870 088

(16 row(s) affected)

My desired results would look like this:

patID txtCode(s)
---------- ------------------------------------------------------------------------------------------------------------------------
547385 001, 002, 01B, 026
1094870 001, 002, 007, 009, 015, 016, 017, 018, 020, 025, 026, 088

(2 row(s) affected)

I have found using this code how to do the above one at a time but it would be ideal to return all records:
DECLARE @str varchar(4000)
DECLARE @patID INT
SET @str = (SELECT txtCode + ', ' FROM table1 WHERE patID IN(547385) FOR XML PATH(''))
SET @patID = (SELECT patID FROM table1 WHERE patID IN(547385) GROUP BY patID)
SET @str = SUBSTRING(@str,1,LEN(@str)-1)
SELECT @patID AS 'patID', @str AS 'txtCode(s)'
GO

I’m thinking some sort of loop but am not sure how to get there…Any help would be much appreciated – thx.

 

[Gnism]

With a little modification of the code from PESO found here:

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

I put this together and now have a workable solution to my problem…

SELECT patID, STUFF(g.y, 1, 1, '') AS 'DisCode'
FROM
(
SELECT patID, txtCode
FROM Table1
WHERE patID IN(547385,1094870)
) AS T1

CROSS APPLY
(
SELECT DISTINCT ',' + CAST(txtCode AS VARCHAR(40))
FROM Table1 AS tmpT
WHERE tmpT.patID = T1.patID
ORDER BY ',' + CAST(txtCode AS VARCHAR(40))
FOR XML PATH('')
) AS g

GROUP BY patID, STUFF(g.y, 1, 1, '')

 

[tran008]

select patID, txtCode into #table1 from (
select
547385 as patID, '001' as txtCode
union all
select
547385 , '002'
union all
select
547385 , '01B'
union all
select
547385 , '026'
union all
select
1094870 , '001'
union all
select
1094870 , '002'
union all
select
1094870 , '007'
union all
select
1094870 , '009'
union all
select
1094870 , '015'
union all
select
1094870 , '016'
union all
select
1094870 , '017'
union all
select
1094870 , '018'
union all
select
1094870 , '020'
union all
select
1094870 , '025'
union all
select
1094870 , '026'
union all
select
1094870 , '088'
)B


SELECT DISTINCT t1.patID,
STUFF((SELECT ',' + cast(t2.txtCode as varchar) FROM #table1 AS t2 WHERE t2.patID = t1.patID FOR XML PATH('')), 1, 1, '') AS txtCode
FROM #table1 AS t1
ORDER BY t1.patID

drop table #table1

 

[simian336]

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

Simi