sql从sql 2005中提取一个csv列表

问题描述

大家好，

我在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"

推荐答案

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

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