在SQL Server中将多行合并为一行 | Server中将多行合并为一行

本文介绍了在SQL Server中将多行合并为一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

三行

There are three rows

      Id  marks
Row1: 1   15
Row2: 1   16
Row3: 1   17

而且我想将所有三行合并为一行，请问有人可以帮忙!!!!

输出应如下所示

and I want to merge all the three rows into a single row, can anybody help me please!!!!

output should be like below

      Id  marks1 marks2 marks3
Row1: 1       15   16      17

请帮帮我..
谢谢.

please help me..
thank you.

推荐答案

      Id  MarkId  Mark
Row1  1   1       15
Row2  1   2       16
Row3  1   3       17
Row4  2   1       25
Row5  2   2       26
Row6  2   3       27

然后，您可以使用PIVOT旋转选择.

You can then use PIVOT to rotate the selection.

SELECT Id, [1], [2], [3]
FROM
    (SELECT Id, MarkId, Mark FROM MarksTable) AS SourceTable
    PIVOT
    (Min(Mark) FOR MarkId IN ([1], [2], [3])) AS PivotTable

这将输出如下:

This will output like this:

Id  "1"  "2"  "3"
1   15   16   17
2   25   26   27

以下是遵循您的示例并可以正常工作的完整复制和粘贴脚本:

The following is a full copy and paste script which follows your example and works:

SELECT Id = 1, MarkId = 1, Mark = 15 INTO #Temp UNION
SELECT Id = 1, MarkId = 2, Mark = 16 UNION
SELECT Id = 1, MarkId = 3, Mark = 17 UNION
SELECT Id = 2, MarkId = 1, Mark = 25 UNION
SELECT Id = 2, MarkId = 2, Mark = 26 UNION
SELECT Id = 2, MarkId = 3, Mark = 27

SELECT * FROM #Temp

SELECT Id, [1], [2], [3]
FROM
    (SELECT Id, MarkId, Mark FROM #Temp) AS SourceTable
    PIVOT
    (Min(Mark) FOR MarkId IN ([1], [2], [3])) AS PivotTable

DROP TABLE #Temp

使用动态查询，可以通过动态生成SQL并使用SP_ExecuteSQL运行它来建立n个标记的选择.

下面的文章显示了如何使用动态SQL构建PIVOT查询

http://www.simple-talk.com/community/博客/andras/archive/2007/09/14/37265.aspx [ ^ ]

Using a dynamic query you can build up a select for n number of marks by generating the SQL on the fly and running it using SP_ExecuteSQL.

The following article shows how to build a PIVOT query using dynamic SQL

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx[^]

这篇关于在SQL Server中将多行合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！