我有一个查询输出，我希望在任何人的帮助下输出的一个小变化。 [英] I have a query output and I want a small change in the ouput can anyone help.?

问题描述

大家好，



我有这样的查询输出，

------------ -------------

状态PP AR BL EN 
有效3 2 2 1 
无效1 2 2 3 

------------------

但我想要输出像这样，

状态总计PP AR BL EN 
有效8 3 2 2 1 
无效8 1 2 2 3 

任何帮助表示赞赏。



注意：这里的总计列是所有团队的总和数量。



我尝试过的事情：

  SELECT 状态，[PP]，[AR]，[BL]，[EN]  FROM  
（ SELECT 状态，团队 FROM  tablename） as  src 
 PIVOT 
（
 COUNT（团队） FOR 团队 IN （[PP]，[AR]，[BL]，[EN]）
） AS  输出 

解决方案

使用CTE表获取每个状态的计数，然后将其加入用于pivot的原始表，即：

 WITH CTETABLE（状态，总计）为
（
 SELECT状态，来自tablename group的count（status）状态
 
）SELECT状态，总计，[PP]，[AR]，[BL]，[EN] FROM 
（SELECT c.status，c.total，team FROM tablename t在t.status = c上加入CTETABLE c .status）as src 
 PIVOT 
（
 COUNT（团队）FOR团队IN（[PP]，[AR]，[BL]，[EN]）
）AS输出

另一种方式（替代 Peter Leow [ ^ ]）是：

  SELECT 状态，[PP] + [AR] + [BL] + [EN]  AS 总计，[PP]，[AR]，[BL]，[EN] 
  FROM （
  SELECT 状态，[PP]，[AR]，[BL ]，[EN] 
  FROM （
  SELECT 状态，团队 FROM  tablename 
） as  src 
 PIVOT（COUNT（team） FOR  team  IN （[PP]，[AR]，[BL]，[EN]）） AS  输出 
） AS 最终

Hi guys,

I have a query output like this,
-------------------------

Status	   PP	AR	BL	EN
Active	   3	2	2	1
In-Active	1	2	2	3

------------------
But i want the output like this,

Status    Total PP AR BL EN
Active    8     3   2  2  1
In-Active 8     1   2  2  3

Any help is appreciated.

NOTE: Here the "Total" column is the sum of the all teams count.

What I have tried:

SELECT status, [PP],[AR],[BL], [EN] FROM
(SELECT status, team FROM tablename) as src
PIVOT
(
   COUNT(team) FOR team IN ([PP],[AR],[BL], [EN])
) AS output

解决方案

Use a CTE table to get the count of each status, then join it with the original table for pivot, i.e.:

WITH CTETABLE (status, total) as
(
    SELECT status, count(status) from tablename group by status
    
) SELECT status, total, [PP],[AR],[BL], [EN] FROM
(SELECT c.status, c.total, team FROM tablename t join CTETABLE c on t.status=c.status) as src
PIVOT
(
   COUNT(team) FOR team IN ([PP],[AR],[BL], [EN])
) AS output

Another way (alternative to Peter Leow[^]) is:

SELECT status, [PP]+[AR]+[BL]+[EN] AS Total, [PP], [AR], [BL], [EN]
FROM (
    SELECT status, [PP],[AR],[BL], [EN]
    FROM (
        SELECT status, team FROM tablename
        ) as src
    PIVOT (COUNT(team) FOR team IN ([PP],[AR],[BL], [EN])) AS output
) AS Final

这篇关于我有一个查询输出，我希望在任何人的帮助下输出的一个小变化。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！