如何在SQL中展开列 | 如何在SQL中展开列

本文介绍了如何在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.

推荐答案

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

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