如何在SQL Server中的单个列中选择结果行

问题描述：

i have two tables  table 1 get single value and table 2 more than value in same id

I have a table like this:

+-------+------+------+
|No     |code  | qty  |
+-------+------+------+
|1055   |956   |  10  |
+-------+------+------+
|1055   |957   |  9   |
+-------+------+------+
|1055   |958   |  5   |
+-------+------+------+
|1055   |959   |  4   |
+-------+------+------+
|1055   |960   |  3   |
+-------+------+-----+
 
i need rows value in single column without using sub query
The output should be something like:

+------+----------------+----------+
|No    |code   |           qty     |
+------+----------------+----------+
|1055  |956,957,958,959 | 10,9,5,4 |
+------+------+---------+----------+

how to get value using sql server

我尝试过：

What I have tried:

 SELECT   Stuff( (SELECT N','+ table1.code
 from table1   inner join table2 on  table1.no=table2.no
where table1.trackno in ('17-05-SC-0009') order by table1.trackno desc
     For XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'),1,1,N'') as [MobileNos]

答

你唯一遗漏的是query是 GROUP BY 子句，但是使用FOR XML PATH你将不得不使用相关的子查询。 ie

The only thing you have missed in your query is the GROUP BY clause, but using FOR XML PATH you are going to have to use correlated sub-queries I think. i.e.

SELECT
    t1.[no]
        ,STUFF((SELECT ', ' + cast(t2.code as varchar)
               FROM test t2
               WHERE t1.[no]=t2.[no] ORDER BY t2.code
               FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),1,1, N'') AS codes

        ,STUFF((SELECT ', ' + cast(t2.qty as varchar)
               FROM test t2
               WHERE t1.[no]=t2.[no] ORDER BY t2.code
               FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),1,1, N'') AS qtys
FROM test t1
GROUP BY t1.[no]

哪个给出结果

No       Codes                           Qtys
1055	 956, 957, 958, 959, 960	 10, 9, 5, 4, 3
1056	 958, 959	                 5, 4

[注意 - 我刚刚创建了一个表格测试，其中包含了问题中的数据 - 将test替换为您的表格加入]

如果你绝对不能使用子查询，那么你可以使用COALESCE，但是我能够工作的唯一方法是使用循环（yuk！），像这样

[NOTE - I just created a table test that had your data from the question in it - replace "test" with your table join]

If you absolutely cannot use sub-queries then you can use COALESCE but the only way I could get that to work was with a loop (yuk!) like this

DECLARE @start int = (SELECT CAST(MIN([No]) AS Int) FROM test)
DECLARE @end int = (SELECT CAST(MAX([No]) AS Int) FROM test)
DECLARE @results table ([no] varchar(4), codes varchar(max), qtys varchar(max))
WHILE @start <= @end
BEGIN
	DECLARE @listCode VARCHAR(MAX) = null
	DECLARE @listQty VARCHAR(MAX) = null
	SELECT  @listCode = COALESCE(@listCode+',' ,'') + CAST(code AS Varchar),
		@listQty = COALESCE(@listQty+',' ,'') + CAST(qty AS Varchar)
	FROM test
	WHERE [no] = CAST(@start AS Varchar)
	
	IF @listCode IS NOT NULL
	BEGIN
		INSERT INTO @results  ([no], codes, qtys) VALUES (cast(@start as varchar), @listCode, @listQty)
	END

	SET @start += 1
END
SELECT * FROM @Results

如何在SQL Server中的单个列中选择结果行

相关推荐