在SQL Server中将多行合并为一行 [英] combine multiple rows into one row in 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.

推荐答案

听起来您需要枢轴旋转"

使用PIVOT sql命令，您可以旋转和重组数据，尽管我不确定您是否可以将它作为静态查询用于n列.

您在标记表中需要一个额外的ID，因此针对每个ID的每个标记也具有自己的顺序.因此主键将是id，markid.

It sounds like you need to "pivot"

Using the PIVOT sql command you can rotate and restructure the data although I''m not sure you can get it working for n columns as a static query.

You need an extra id in the marks table so each mark against each id has own sequence too. So the primary key would be id, markid.

      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中将多行合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！