sql从sql 2005中提取一个csv列表 [英] sql extract a csv list from sql 2005

问题描述

大家好，

我在sql server 2005中遇到"csv字段"生成的问题.

情况是:
3桌:
[要求] idReq
[RequestProcess] idReq，idProc
[进程] idProc，说明

一个请求可以有多个流程.

我想要一张
表 idReq-CsvProc


我能够为给定的idReq生成csv，但是我无法列出idReq-CsvProc.

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.

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,'')

有什么想法要继续吗?

我不熟悉xml路径查询

谢谢


更新:
一个真实的例子

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"

推荐答案

尝试一下

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

您是否尝试过Google?
我得到了一些可以帮助您解决问题的东西.

参见下面的链接...
http://www.google.co.in/search?hl=zh_CN& sclient = psy-ab& q = GROUP_CONCAT + FUNCTION& btnG = [ ^ ]
^ ]

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[^]

这篇关于sql从sql 2005中提取一个csv列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！