如何在SQL中展开列 [英] How to spread out the column in SQL

问题描述

我有一张如下表格。

Hi,

I have a table like this below.

TaskId StatusId StatusName TypeId
1         189       New       1
2         199       New1      2
3         189       New       1
4         199       New1      3
5         190       new2      4
6         190       new2      4

我需要输出如下

I need output as below

TypeId   New  New1  New2
1         2    0     0
2         0    1     0
3         0    1     0
4         0    0     2

提前致谢。



我尝试过：

Thanks in advance.

What I have tried:

CREATE TABLE #TaskTable
(TaskId INT, StatusId INT, StatusName VarChar(50), TypeId INT);
GO
 
INSERT INTO #TaskTable VALUES(1,189,'New',1);
INSERT INTO #TaskTable VALUES(2,199,'New1',2);
INSERT INTO #TaskTable VALUES(3,189,'New',1);
INSERT INTO #TaskTable VALUES(4,199,'New1',3);
INSERT INTO #TaskTable VALUES(5,190,'New2',4);
INSERT INTO #TaskTable VALUES(6,190,'New2',4);

SELECT *
FROM #TaskTable

SELECT *
FROM #TaskTable
  PIVOT(Count(StatusId)  
  FOR StatusName IN([New], [New1],[New2]) 
  ) P;

但结果不符合预期。

But result is not as expected.

推荐答案

你必须对结果进行分组by typeid ...

You have to group the result by typeid...

WITH PIVOTED
AS (
	SELECT *
	FROM #TASKTABLE
		PIVOT(COUNT(STATUSID)  
			FOR STATUSNAME IN([NEW], [NEW1],[NEW2]) 
		) P
)
SELECT 
	TYPEID,
	SUM(NEW) AS NEW,
	SUM(NEW1) AS NEW1,
	SUM(NEW2) AS NEW2
FROM PIVOTED
GROUP BY TYPEID

select * from（

SELECT TypeId，

StatusId，

StatusName FROM #TaskTable

）as gg



PIVOT（

Count（StatusId）

FOR StatusName IN（

[新]，

[New1 ]，

[New2]）

）P;

select * from (
SELECT TypeId,
StatusId,
StatusName FROM #TaskTable
)as gg

PIVOT(
Count(StatusId)
FOR StatusName IN(
[New],
[New1],
[New2])
) P;

这篇关于如何在SQL中展开列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！