如何在sql server 2008中将多行作为单行返回 [英] How can I return multiple rows as a single row in 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 [ ^ ]功能;）

示例：< a href =http://stackoverflow.com/questions/18644369/pivot-multiple-columns-into-rows-in-sql-server>将多个列转换为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 ，< span class =code-string>'  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）
 < span class =code-keyword> 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.

这篇关于如何在sql server 2008中将多行作为单行返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！