sql extract a csv list from sql 2005

Question

4.00/5 (1 vote)

See more:

Hi guys,

i'm facing a problem with a "csv field" generation in sql server 2005.

the situation is that :
3 tables :
[Requests] idReq
[RequestProcess] idReq, idProc
[Processes] idProc, Description

a request can have more than one process.

i'd like to have a table of
idReq - CsvProc

i'm able to generate a csv for a given idReq, but i cant make a list of idReq - CsvProc.

SQL

SELECT STUFF(
	(
		SELECT	',' + Processes.Description
				
		FROM	Processes 
		
		JOIN	RequestProcess ON
			RequestProcess.idProc = Processes.idProc 
		
		WHERE	RequestProcess.idReq =  158

		ORDER BY Processes.Description

		FOR XML PATH ('')
	),1,1,'')

any idea to continue ?

i'm not familiar with xml path quering

thanks

UPDATE:
a real example

[Requests] 
idReq | Descr
150 | Test A
151 | Test B
152 | Test C

[Processes] 
idProc, Description
1 | A
2 | B
3 | C

[RequestProcess]
idReq, idProc
150 | 1
150 | 2 
150 | 3
151 | 3
152 | 1
152 | 3

[Result]
idReq | CsvProc
151 | "A, B, C" 
152 | "C" 
153 | "A, C"

Posted 23-Aug-12 22:27pm

nrgjack

Updated 24-Aug-12 4:57am

v5

Add a Solution

Comments

ZurdoDev 24-Aug-12 10:28am

I'm having a hard time visioning what the end result is. Can you explain what string you expect? I think you just need to add in the other field you want.

2 solutions

Solution 1

have you tried on Google ?
i got something which will help you to solve problem..

see below link...
http://www.google.co.in/search?hl=en&sclient=psy-ab&q=GROUP_CONCAT+FUNCTION&btnG=[^]
http://stackoverflow.com/questions/3492539/mysql-multiple-row-as-comma-separated-single-row[^]
http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator[^]

Posted 24-Aug-12 6:31am

ssd_coolguy

Comments

nrgjack 26-Aug-12 16:26pm

yep i googled before posting, i've found a lot of stored/functions like the one posted in stackoverflow, but i need to put this query in join with other stuffs and so mi question was a little more precise,
the group_concat function could be a valid option but i see that is for mysql db and the reference inside the link to see how the group concat have to be made is broken.

anyway thanks for your help :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Accepted Answer · 2012-08-24T06:35:00

Try this

CREATE TABLE #Requests (idReq INT, Descr VARCHAR(50))

CREATE TABLE #RequestProcess (idReq INT, idProc INT)

CREATE TABLE #Processes (idProc INT, Description VARCHAR(50))

INSERT INTO #Requests
SELECT 150, ' Test A'UNION 
SELECT 151, ' Test B'UNION 
SELECT 152, ' Test C'


INSERT INTO #RequestProcess
SELECT 150, 1 UNION 
SELECT 150, 2 UNION 
SELECT 150, 3 UNION 
SELECT 151, 3 UNION 
SELECT 152, 1 UNION 
SELECT 152, 3 



INSERT INTO #Processes
SELECT 1, 'A' UNION 
SELECT 2, 'B' UNION 
SELECT 3, 'C'





SELECT idReq,
SUBSTRING(
(SELECT ', ' + Description FROM #RequestProcess RP2
INNER JOIN #Processes P ON RP2.idProc = P.idProc
WHERE RP1.idReq = RP2.idReq
FOR XML PATH('')), 3,1000) AS CsvProc
FROM #RequestProcess RP1
GROUP BY IdReq




DROP TABLE  #Requests
DROP TABLE  #RequestProcess
DROP TABLE  #Processes