Getting comma seperated values without while loop

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)

Getting comma seperated values without while loop

Author

Topic

kalluri_nb
Starting Member

7 Posts

Posted - 2008-09-24 : 10:52:44

IN THE BELOW EXAMPLE HOW CAN I GET A RESULT SET LIKE
1 A,B,C
2 B
3 B,C
4 A,C

CREATE TABLE TESTTABLE
(
ID INT,
NAME VARCHAR(50)
)

INSERT INTO TESTTABLE VALUES(1,'A')
INSERT INTO TESTTABLE VALUES(1,'B')
INSERT INTO TESTTABLE VALUES(1,'C')
INSERT INTO TESTTABLE VALUES(2,'B')
INSERT INTO TESTTABLE VALUES(3,'B')
INSERT INTO TESTTABLE VALUES(3,'C')
INSERT INTO TESTTABLE VALUES(4,'A')
INSERT INTO TESTTABLE VALUES(4,'C')

SELECT *FROM TESTTABLE
DECLARE @STR VARCHAR(8000)
SET @STR=''
SELECT @STR = @STR + COALESCE(','+NAME,'') FROM TESTTABLE
PRINT @STR
SET @STR=''

WHATS WRONG WITH BELOW QUERY
SELECT DISTINCT A.ID,NAMES = (SELECT @STR = @STR + COALESCE(','+B.NAME,'') FROM TESTTABLE B WHERE B.ID=A.ID)
FROM TESTTABLE A
PRINT @STR
DROP TABLE TESTTABLE

IS WHILE LOOP ONLY THE OPTION

Regards,
Narotham

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 10:58:16

[code]SELECT t.ID,LEFT(nl.namelist,LEN(nl.namelist)-1) AS Names
FROM (SELECT DISTINCT ID FROM TESTTABLE)t
CROSS APPLY (SELECT NAME + ',' AS [text()]
FROM TESTTABLE
WHERE ID=t.ID
FOR XML PATH(''))nl(namelist)[/code]

kalluri_nb
Starting Member

7 Posts

Posted - 2008-09-24 : 11:44:52

Thank you visakh for the solution. can you please explain me what we were doing there and cross apply and text() mean

Regards,
Narotham

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 11:52:58

quote:
Originally posted by kalluri_nb

Thank you visakh for the solution. can you please explain me what we were doing there and cross apply and text() mean

Regards,
Narotham

Cross Apply basically allows you to get correlated resultset for each row value of main query. Here we are taking distinct IDs in main query and then using CROSS APPLY finding out matching names as a xml list for each ID value. the right side of CROSS APPLY builds a xml list by means of FOR XML PATH feature of SQL 2005.text() returns the result in text format to obtain concatenation effect.

kalluri_nb
Starting Member

7 Posts

Posted - 2008-09-25 : 05:07:59

Thank you visakh very much

Regards,
Narotham

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:09:49