Sql server枢轴和合并行 [英] Sql server pivot and merge rows

问题描述

问题是我找不到将行合并到一列的方法。就像这样在prev1



描述Catgry ItemCode badder 001CAL 002BAL 003VAL

＃2 Avery Tag H000021803 6445 1 NULL NULL

C002-A HBW Ballpen Black M000004820 6446 NULL 1 NULL

C002-A HBW Ballpen Black M000004820 6447 NULL NULL 144

C002-A HBW Ballpen Blue M000040437 6446 NULL 1 NULL

C002-A HBW Ballpen Blue M000040437 6447 NULL NULL 144

C002-A HBW Ballpen Red M000040438 6446 NULL 1 NULL

C002- A HBW Ballpen Red M000040438 6447 NULL NULL 144

C009-A马尼拉纸CAT B M000005161 6445 1 NULL NULL

C009-A马尼拉纸CAT B M000005161 6446 NULL 1 NULL

C009-A马尼拉纸CAT B M000005161 6447 NULL NULL 144



但我想预览2是..

描述Catgry ItemCode badder 001CAL 002BAL 003VAL

＃2 Avery Tag H000021803 6445 1 NULL NULL

C002-A HBW Ballpen Black M000004820 6446,6447 NULL 1 144

C002-A HBW Ballpen Blue M000040437 6446,6447 NULL 1 144
C002-A HBW Ballpen Red M000040438 6446,6447 NULL 1 144

C009-A马尼拉纸CAT B M000005161 6445,6446,6447 1 1 144



我尝试了什么：



i使用pivot on prev1但是当我显示colonm badder时它没有合并但是将它分组并且我也使用子查询来合并行但是没有好的合并所有穷人。

Problem is i cant find a way to merge rows into one column. just like this in prev1

Descriptions Catgry ItemCode poorder 001CAL 002BAL 003VAL
#2 Avery Tag H000021803 6445 1 NULL NULL
C002-A HBW Ballpen Black M000004820 6446 NULL 1 NULL
C002-A HBW Ballpen Black M000004820 6447 NULL NULL 144
C002-A HBW Ballpen Blue M000040437 6446 NULL 1 NULL
C002-A HBW Ballpen Blue M000040437 6447 NULL NULL 144
C002-A HBW Ballpen Red M000040438 6446 NULL 1 NULL
C002-A HBW Ballpen Red M000040438 6447 NULL NULL 144
C009-A Manila Paper CAT B M000005161 6445 1 NULL NULL
C009-A Manila Paper CAT B M000005161 6446 NULL 1 NULL
C009-A Manila Paper CAT B M000005161 6447 NULL NULL 144

but i want to preview2 is..
Descriptions Catgry ItemCode poorder 001CAL 002BAL 003VAL
#2 Avery Tag H000021803 6445 1 NULL NULL
C002-A HBW Ballpen Black M000004820 6446,6447 NULL 1 144
C002-A HBW Ballpen Blue M000040437 6446,6447 NULL 1 144
C002-A HBW Ballpen Red M000040438 6446,6447 NULL 1 144
C009-A Manila Paper CAT B M000005161 6445,6446,6447 1 1 144

What I have tried:

i use pivot on prev1 but when i show colonm poorder it does not merging but group it self and also i use subquery with merging rows but no good cuz it merging all poorder.

推荐答案

假设你的表看起来像这样：

Assuming your table looks like this:

create table prev1
(
	Descriptions nvarchar(125),
	ItemCode nvarchar(125),
	poorder int,
	[001CA] int,
	[002BAL] int,
	[003VAL] int
)
insert into prev1 values
('#2 Avery Tag'			,'H000021803', 6445, 1, NULL, NULL ),
('C002-A HBW Ballpen Black'	,'M000004820', 6446, NULL, 1, NULL ),
('C002-A HBW Ballpen Black'	,'M000004820', 6447, NULL, NULL, 144 ),
('C002-A HBW Ballpen Blue'	,'M000040437', 6446, NULL, 1, NULL ),
('C002-A HBW Ballpen Blue'	,'M000040437', 6447, NULL, NULL, 144 ),
('C002-A HBW Ballpen Red'	,'M000040438', 6446, NULL, 1, NULL ),
('C002-A HBW Ballpen Red'	,'M000040438', 6447, NULL, NULL, 144 ),
('C009-A Manila Paper CAT B'	,'M000005161', 6445, 1, NULL, NULL ),
('C009-A Manila Paper CAT B'	,'M000005161', 6446, NULL, 1, NULL ),
('C009-A Manila Paper CAT B'	,'M000005161', 6447, NULL, NULL, 144 )

然后您可以使用此查询：

Then you can use this query:

;WITH CTE AS
(
	SELECT ItemCode, poorder = 
    STUFF((SELECT ', ' + CAST(poorder as nvarchar)
           FROM prev1 p1
           WHERE p1.ItemCode = p2.ItemCode 
          FOR XML PATH('')), 1, 2, '')
	FROM prev1 p2
	GROUP BY ItemCode
)
SELECT Descriptions, P.ItemCode, CTE.poorder, [001CA] = SUM([001CA]),[002BAL] = SUM([002BAL]),[003VAL] = SUM([003VAL])
FROM prev1 P
INNER JOIN CTE ON P.ItemCode=CTE.ItemCode
GROUP BY Descriptions, P.ItemCode, CTE.poorder

给出结果

#2 Avery Tag		  H000021803	6445		 1	NULL	NULL
C002-A HBW Ballpen Black  M000004820	6446, 6447	 NULL	1	144
C002-A HBW Ballpen Blue   M000040437	6446, 6447	 NULL	1	144
C002-A HBW Ballpen Red	  M000040438	6446, 6447	 NULL	1	144
C009-A Manila Paper CAT B M000005161	6445, 6446, 6447 1	1	144

说明：

CTE（公用表表达式）连接各种[穷人] ]值为每个ItemCode的逗号分隔列表，如下所示：

Explanation:
The CTE (Common Table Expression) is concatenating the various [poorder] values into a comma-separated list for each ItemCode like this:

00021803	6445
M000004820	6446, 6447
M000005161	6445, 6446, 6447
M000040437	6446, 6447
M000040438	6446, 6447

然后我们将其加入原始表格。不需要转动，因为我们可以使用SUM

We then join that back to the original table. There is no need to pivot as we can get the other column values with SUM

这篇关于Sql server枢轴和合并行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！