SQL Server 将 2 行合并为 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15635193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Merge 2 rows into 1
提问by Spacko
Is it possible to merge 2 rows into a single row in SSRS 2008? Each part will have a record for each site
是否可以在 SSRS 2008 中将 2 行合并为一行？每个部分每个站点都会有一个记录
+---------------+-------+-------+
|Part Number |Cost |Site |
+---------------+-------+-------+
|1 |2.4 |Site 1 |
|1 |68.8 |Site 2 |
+---------------+-------+-------+
Desired Result
想要的结果
+-----------+-------+-------+
|Part Number|Site 1 |Site 2 |
+-----------+-------+-------+
| 1 |2.4 |68.8 |
+-----------+-------+-------+
Thank you
谢谢
回答by Fabio
If you know your site numbers/names will not change dynamically then can use CASE WHEN:s
如果您知道您的站点编号/名称不会动态更改，则可以使用CASE WHEN:s
SELECT PartNumber,
MAX(CASE WHEN Site=1 THEN Cost ELSE NULL END) AS Site1_Cost,
MAX(CASE WHEN Site=2 THEN Cost ELSE NULL END) AS Site2_Cost
FROM Parts
GROUP BY PartNumber
By grouping we eliminated a NULL values...
通过分组，我们消除了一个 NULL 值......
Here linkwith SQL Fiddle example
这里链接与 SQL Fiddle 示例
回答by praveen
In SSRS to you need to use MATRIXreport to convert rows into columns without using PIVOT operator
在 SSRS 中，您需要使用MATRIX报表将行转换为列而不使用PIVOT operator
Suppose you have a table SSRSPivot
假设你有一张桌子 SSRSPivot
Create table SSRSPivot
(PartNumber int ,Cost decimal(18,2),Site varchar(max))
Insert into SSRSPivot
values
(1,2.4,'Site 1'),
(1,68.8,'Site 2' )
The data is in the below format
数据格式如下
+---------------+-------+-------+
|PartNumber |Cost |Site |
+---------------+-------+-------+
|1 |2.4 |Site 1 |
|1 |68.8 |Site 2 |
+---------------+-------+-------+
Create a new blank report and name it as PIVOT.Create a datasource and in the dataset write the query
创建一个新的空白报告并将其命名为PIVOT.Create a datasource 并在数据集中编写查询
Select PartNumber ,Cost,Site from SSRSPivot
Drag a matrixfrom the toolbox onto the SSRS designer.For Rowsselect PartNumber.For columns select Siteand for the data select Sum(Cost)
将 amatrix从工具箱拖到 SSRS 设计器上。For Rowsselect PartNumber.For columns selectSite和 for the data selectSum(Cost)
When you do the above step ,you will get the rowand columndetails like the one below
当你做了上面的步骤，你将获得row和column细节，如下面的一个
Final Result will look like
最终结果看起来像
回答by Taryn
This type of data transformation is known as a PIVOT. Starting in SQL Server 2005, there is a function that can transpose the data into columns for you.
这种类型的数据转换称为PIVOT. 从 SQL Server 2005 开始，有一个函数可以为您将数据转换为列。
If you have a known number of Sitevalues that you want to turn into columns, then you can hard-code the query:
如果您有已知数量的Site要转换为列的值，则可以对查询进行硬编码：
select part_number, [Site 1], [Site 2]
from
(
select part_number, cost, site
from yourtable
) src
pivot
(
sum(cost)
for site in ([Site 1], [Site 2])
) piv;
But if you have an unknown number of values, then you will need to use dynamic SQL to generate the column list to be used in the query:
但是如果您有未知数量的值，那么您将需要使用动态 SQL 来生成要在查询中使用的列列表：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(site)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT part_number,' + @cols + '
from
(
select part_number, cost, site
from yourtable
) x
pivot
(
sum(cost)
for site in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo. Both will give the result:
请参阅SQL Fiddle with Demo。两者都会给出结果：
| PART_NUMBER | SITE 1 | SITE 2 |
---------------------------------
| 1 | 2.4 | 68.8 |
