如何在sql server 2008中将多行作为单行返回

本文介绍了如何在sql server 2008中将多行作为单行返回的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的事情：

mtm_id package_id attribute_id value 10708 148323 23 a 10708 148323 35 12 10708 148323 39 20 10708 148323 46 12 10708 148323 50 1

我要查询的是：

mtm_id package_id value1 value2 value3 value4 value5 10708 148323 a 12 20 12 1

我需要一些东西才能得到所有一次排.. 提前感谢..

解决方案

真的没有办法做到这一点使用pivot，除非您确切知道要查找的值的数量，并且可以命名它们。这个SQL适用于任意数量的值，但结果是，它将列表中的值拉出到一个字段中：

选择 distinct mtm_id，package_id， stuff（（选择 ' ，' + ' （' + cast（attribute_id as varchar （ 10 ））+ ' ： ' + value + ' ）' 来自 Rashid r1 其中 r.mtm_id = r1.mtm_id 和 r.package_id = r1.package_id FOR XML PATH（' '））， 1 ， 2 ，' '） as item 来自 rashid r

如果您只需要属性，执行此操作：

选择 mtm_id，package_id， stuff（（ select ' ，' + value 来自 Rashid r1 其中 r.mtm_id = r1.mtm_id 和 r.package_id = r1.package_id FOR XML PATH（' '））， 1 ， 2 ，' '） as items from rashid r group 按 mtm_id，package_id

另请注意我第二次使用'group by'来摆脱第一个例子中'DISTINCT'的可怕使用。如果您愿意，可以对第一个示例进行此更改。

另一种方法是使用 ROW_NUMBER（） [ ^ ]功能： 测试它：

CREATE TABLE #test（mtm_id INT ，package_id INT ，attribute_id INT ，值 VARCHAR （ 30 ）） INSERT INTO #test（mtm_id，package_id，attribute_id，value） VALUES （ 10708 ， 148323 ， 23 ，' a'），（ 10708 ， 148323 ， 35 ，' 12'），（ 10708 ， 148323 ， 39 ，' 20'），（ 10708 ， 148323 ， 46 ，' 12'），（ 10708 ， 148323 ， 50 ，' 1'） SELECT mtm_id， package_id，[ 1 ]，[ 2 ]，[ 3 ]，[ 4 ]，[ 5 ] ，[ 6 ]，[ 7 ] FROM （ SELECT ROW_NUMBER（） OVER （ PARTITION BY mtm_id ORDER BY attribute_id） AS RowNo，mtm_id，package_id，[value] FROM #test ） AS DT PIVOT（MAX（[value]） FOR RowNo IN （[ 1 ]，[ 2 ]，[ 3 ]，[ 4 ]，[ 5 ]，[ 6 ]， [ 7 ]）） AS PT DROP 表 #test

注意：在上面的示例中，手动添加了列数。它将通过使用 STUFF [ ^ ]功能;） 示例：将多个列转换为sql server中的行 [ ^ ] 结果：

mtm_id p .._ id [1] [2] [3] [4] [5 ] [6] [7] 10708 148323 a 12 20 12 1 NULL NULL

动态版 ;）

CREATE TABLE #test（mtm_id INT ，包_id INT ，attribute_id INT ，值 VARCHAR （ 30 ）） INSERT INTO #test（mtm_id，package_id，attribute_id，value） VALUES （ 10708 ， 148323 ， 23 ，' a'），（ 10708 ， 148323 ， 35 ，' 12'），（ 10708 ， 148323 ， 39 ，' 20'），（ 10708 ， 148323 ， 46 ，' 12'），（ 10708 ， 148323 ， 50 ，' 1'）， （ 10709 ， 148323 ， 39 ，' b'），（ 10710 ， 148323 ， 46 ，' c'），（ 10710 ， 148323 ， 46 ，' 1'），（ 10710 ， 148323 ， 46 ，' 3'），（ 10710 ， 148323 ， 46 ， ' 5'），（ 10710 ， 148323 ， 46 ，' 7'），（ 10710 ， 148323 ， 46 ，' 9'），（ 10710 ， 148323 ， 46 ，' 11'） DECLARE @cols VARCH AR （ 300 ）= ' ' DECLARE @ dt VARCHAR （ 2000 ）= ' ' DECLARE @ pt VARCHAR （MAX）= ' ' SET @cols = STUFF（（ SELECT DISTINCT ' ]，[' + CONVERT （ VARCHAR （ 10 ），C.RowNo） FROM （ SELECT ROW_NUMBER（） OVER （ PARTITION BY mtm_id ORDER BY attribute_id） AS RowNo，mtm_id，package_id，[value] FROM ＃ test ）C FOR XML PATH（' '））， 1 ， 2 ，' '）+ ' ]' - SET @cols + =']' - SELECT @cols AS Cols SET @ dt = ' SELECT ROW_NUMBER（）OVER（PARTITION BY mtm_id ORDER BY attribute_id）AS RowNo，mtm_id，package_id，[value]' + ' FROM #test' - EXEC （@dt） SET @ pt = ' SELECT mtm_id，package_id，' + @cols + ' FROM（' + @ dt + ' ）AS DT' + ' PIVOT（MAX（[value]）FOR RowNo IN（' + @ co ls + ' ））AS PT' - PRINT @pt EXEC （ @ pt ） DROP TABLE #test

[/ EDIT]

查看 PIVOT [ ^ ]功能。

i have something like this:

mtm_id package_id attribute_id value 10708 148323 23 a 10708 148323 35 12 10708 148323 39 20 10708 148323 46 12 10708 148323 50 1

What I'd like is to query back:

mtm_id package_id value1 value2 value3 value4 value5 10708 148323 a 12 20 12 1

I need something to get all the rows at once.. thanks in advance..

解决方案

There's really no way to do this using pivot, unless you know exactly the number of values you're looking for, and can name them. This SQL will work for any number of values, but as a result, it pulls out the values in your list in to one field:

select distinct mtm_id, package_id, stuff((select ', ' + '(' + cast(attribute_id as varchar(10)) + ':' + value + ')' from Rashid r1 where r.mtm_id = r1.mtm_id and r.package_id = r1.package_id FOR XML PATH('')),1,2,'') as items from rashid r

If you just need the attributes, do this:

select mtm_id, package_id, stuff((select ', ' + value from Rashid r1 where r.mtm_id = r1.mtm_id and r.package_id = r1.package_id FOR XML PATH('')),1,2,'') as items from rashid r group by mtm_id, package_id

Note also that the second time I used 'group by' to get rid of the awful use of 'DISTINCT' in the first example. This change can be made to the first example, if you wanted.

Another way is to use ROW_NUMBER()[^] function: Test it:

CREATE TABLE #test (mtm_id INT, package_id INT, attribute_id INT, value VARCHAR(30)) INSERT INTO #test (mtm_id, package_id, attribute_id, value) VALUES(10708, 148323, 23, 'a'), (10708, 148323, 35, '12'), (10708, 148323, 39, '20'), (10708, 148323, 46, '12'), (10708, 148323, 50, '1') SELECT mtm_id, package_id, [1], [2], [3], [4], [5], [6], [7] FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY mtm_id ORDER BY attribute_id) AS RowNo, mtm_id, package_id, [value] FROM #test ) AS DT PIVOT (MAX([value]) FOR RowNo IN([1],[2],[3],[4],[5],[6],[7])) AS PT DROP TABLE #test

Note: in above example number of columns were added manually. It would be created 'on the fly' by using STUFF[^] function ;) Example: pivot multiple columns into rows in sql server[^] Result:

mtm_id p.._id [1] [2] [3] [4] [5] [6] [7] 10708 148323 a 12 20 12 1 NULL NULL

[EDIT] dynamic version ;)

CREATE TABLE #test (mtm_id INT, package_id INT, attribute_id INT, value VARCHAR(30)) INSERT INTO #test (mtm_id, package_id, attribute_id, value) VALUES(10708, 148323, 23, 'a'), (10708, 148323, 35, '12'), (10708, 148323, 39, '20'), (10708, 148323, 46, '12'), (10708, 148323, 50, '1'), (10709, 148323, 39, 'b'), (10710, 148323, 46, 'c'), (10710, 148323, 46, '1'), (10710, 148323, 46, '3'), (10710, 148323, 46, '5'), (10710, 148323, 46, '7'), (10710, 148323, 46, '9'), (10710, 148323, 46, '11') DECLARE @cols VARCHAR(300) = '' DECLARE @dt VARCHAR(2000) = '' DECLARE @pt VARCHAR(MAX) = '' SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(VARCHAR(10), C.RowNo) FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY mtm_id ORDER BY attribute_id) AS RowNo, mtm_id, package_id, [value] FROM #test ) C FOR XML PATH('')),1,2,'') + ']' --SET @cols += ']' --SELECT @cols AS Cols SET @dt = 'SELECT ROW_NUMBER() OVER(PARTITION BY mtm_id ORDER BY attribute_id) AS RowNo, mtm_id, package_id, [value] ' + 'FROM #test' --EXEC(@dt) SET @pt = 'SELECT mtm_id, package_id, ' + @cols + ' FROM (' + @dt + ') AS DT ' + ' PIVOT (MAX([value]) FOR RowNo IN(' + @cols + ')) AS PT ' --PRINT @pt EXEC(@pt) DROP TABLE #test

[/EDIT]

Have a look at PIVOT[^] function.