how to get the results of a subquery as a commaseparated list

Question

Post reply

how to get the results of a subquery as a commaseparated list

Karl-452938

Valued Member

Points: 71
More actions
August 26, 2010 at 9:15 am

#223115

We have a database which contains informations about files.
Each file can have from zero to unlimited remarks. We would like to have a comma separated list of the remarks for each of the 50 Files, that where added lately.
We thought about something like that (very simplified):
SELECT TOP (50) dbo.Files.FileID, (SELECT dbo.FileContent.remark FROM dbo.FileContent WHERE (dbo.FileContent.FileID = dbo.Files.FileID)) AS AllRemarks
FROM dbo.Files
ORDER BY dbo.Files.EditedDate DESC
Is there perhaps a command that tells the query, that the result of the subquery is a list instead of a value? Or are there other possibilities to solve this issue?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

We can do it using FOR XML...

Please go through this following article and helping us help you??

CLICK HERE FOR FORUM POSTING ETIQUETTES - BY JEFF MODEN[/url]

When u do so, i am sure a lot of us will help u instantly...

So please post

1. Table Structures - CREATE TABLE scripts

2. Constraints or Indexes in the table, - CREATE SCRIPTS

3. SAMPLE DATA - INSERT INTO TABLE scripts

4. Desired output - some visual representation of this.

Lowell SSC Guru Points: 323518 More actions · Answer 2

this is completely untested, as you didn't provide the schema or sample data.

note i'm assuming there is a column "remarksID" so the list of remarks can be ordered....only YOU know if that column( or it's equivient) exists or not.

i use somethign real similar to get the columns for an index in a comma delimited string so i can make CREATE INDEX statements:

SELECT

TOP (50) dbo.Files.FileID,

RemarksAsXML.AllRemarks

FROM dbo.FileContent

CROSS APPLY

(

SELECT FileContent.remark + ',' + ' '

FROM FileContent

INNER JOIN Files

ON dbo.FileContent.FileID = dbo.Files.FileID

ORDER BY FileID,remarkID --is there

FOR XML PATH('')

) AS RemarksAsXML

ORDER BY dbo.Files.EditedDate DESC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Karl-452938 Valued Member Points: 71 More actions · Answer 3

Complete table definitions would include a lot of stuff, that is not needed here.

Let's keep it simple

We have one table Files, which has 2 fields we need here

-FileID

-EditedDate

1 2010-08-10 08:23

2 2010-07-12 16:12

Sample Data

We have another table FileContent, which has also 2 fields we need here:

-FileID

-remark

Here we can have a lot of remarks. So sample data like that:

1 remark1

1 remark2

1 remark23

1 remark25

2 remark1

2 remark11

As a result we would like to have

1 remark1, remark2, remark23, remark25

2 remark1, remark11

How can we achieve that with FOR XML ?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

FRO XML , can be used like this:

declare @tab table

( id int, remark varchar(100))

insert into @tab

select id, remark from

( select 1 ,'remark1'

union all select 1 ,'remark2'

union all select 1 ,'remark23'

union all select 1 ,'remark25'

union all select 2 ,'remark1'

union all select 2 ,'remark11'

) t(id, remark)

SELECT p1.id,

STUFF ( ( SELECT ','+ remark

FROM @tab p2

WHERE p2.id = p1.id

ORDER BY remark

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values

FROM @tab p1

GROUP BY p1.id ;

Karl-452938 Valued Member Points: 71 More actions · Answer 5

Lowell (8/26/2010)
this is completely untested, as you didn't provide the schema or sample data.
CROSS APPLY
[/code]

Hmmm... CROSS APLLY looks promising as a solution for that problem.

But I hoped there would be an easier way to achieve that something like (SELECT LIST...) or another magic word, thath would help get around the "Subquery returned more than 1 value"-Problem, since we only WANT to have more than one value...;-)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Karl-452938 (8/26/2010)
But I hoped there would be an easier way to achieve that something like (SELECT LIST...)

Karl, did u check the solution posted above your post?

Karl-452938 Valued Member Points: 71 More actions · Answer 7

SELECT ...+ ',' + ' '.... FOR XML PATH('')

did the trick. It seemed a lot more complicated on first sight.

Why should I use CROSS APPLY or STUFF in addition?

WayneS SSC Guru Points: 95461 More actions · Answer 8

STUFF removes the leading comma.

CROSS APPLY runs the query against each record.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 9

You can use CROSS APPLY if you need to retrieve EditedDate in your results set

IF NOT OBJECT_ID('tempdb.dbo.#Files', 'U') IS NULL DROP TABLE #Files

IF NOT OBJECT_ID('tempdb.dbo.#FileContent', 'U') IS NULL DROP TABLE #FileContent

CREATE TABLE #Files (FileID int, EditedDate datetime)

INSERT #Files

SELECT 1, '2010-08-10 08:23' UNION ALL

SELECT 2, '2010-07-12 16:12'

CREATE TABLE #FileContent (FileID int, Remark varchar(100))

INSERT #FileContent

SELECT 1, 'remark1' UNION ALL

SELECT 1, 'remark2' UNION ALL

SELECT 1, 'remark23' UNION ALL

SELECT 1, 'remark25' UNION ALL

SELECT 2, 'remark1' UNION ALL

SELECT 2, 'remark11'

SELECT #Files.FileID, #Files.EditedDate, Z.Remarks

FROM #Files

CROSS APPLY

(

SELECT

STUFF((SELECT ',' + Remark

FROM #FileContent

WHERE #FileContent.FileID = #Files.FileID

ORDER BY Remark

FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, '')

) AS Z (Remarks)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 10

Karl-452938 (8/26/2010)
Complete table definitions would include a lot of stuff, that is not needed here.
Let's keep it simple
We have one table Files, which has 2 fields we need here
-FileID
-EditedDate
1 2010-08-10 08:23
2 2010-07-12 16:12
Sample Data
We have another table FileContent, which has also 2 fields we need here:
-FileID
-remark
Here we can have a lot of remarks. So sample data like that:
1 remark1
1 remark2
1 remark23
1 remark25
2 remark1
2 remark11
As a result we would like to have
1 remark1, remark2, remark23, remark25
2 remark1, remark11
How can we achieve that with FOR XML ?

I agree that complete table definitions aren't required. Complete table definitions to support the problem are (like you tried above).

You got really lucky with having Lowell and ColdCoffee online the same time as you posted. It normally takes quite a bit longer for people to respond without readily consumable data. My recommendation is that you can make it easier on folks like them if you post the data according to the first link in my signature line below. The biggest help is that folks like them have more time to solve problems and providing tested answers instead of setting up data.

Thanks for your help in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)