SQL查询生成数据透视表

Question

我该如何将这个查询转换为数据透视表？

select 
branch,
months,
[Parts Revenue Budget]
from #Temp1 

结果

期望输出

- Sanya Zahid

Answer 1

   SELECT  BRANCH,
   SUM(CASE WHEN MONTHS = 'JAN' THEN PARTSREVENUEBUDJET END) AS [JAN],
   SUM(CASE WHEN MONTHS = 'FEB' THEN PARTSREVENUEBUDJET END) AS [FEB],
  SUM(CASE WHEN MONTHS = 'MAR' THEN PARTSREVENUEBUDJET END) AS [MARCH],
   SUM(CASE WHEN MONTHS = 'APR' THEN PARTSREVENUEBUDJET END) AS [APR],
   SUM(CASE WHEN PONITS = 'MAY' THEN  PARTSREVENUEBUDJET END) AS [MAY],
   SUM(CASE WHEN MONTHS = 'JUNE' THEN PARTSREVENUEBUDJET END) AS [JUNE],
   SUM(CASE WHEN MONTHS = 'JULY' THEN PARTSREVENUEBUDJET END) AS [JULY],
   SUM(CASE WHEN MONTHS = 'AUG' THEN PARTSREVENUEBUDJET END) AS [AUG],
   SUM(CASE WHEN MONTHS = 'SEP' THEN PARTSREVENUEBUDJET END) AS [SEP],
   SUM(CASE WHEN MONTHS = 'OCT' THEN PARTSREVENUEBUDJET END) AS [OCT],
   SUM(CASE WHEN MONTHS = 'NOV' THEN PARTSREVENUEBUDJET END) AS [NOV],
   SUM(CASE WHEN MONTHS = 'NOV' THEN PARTSREVENUEBUDJET END) AS [DEC],
           SUM(PARTSREVENUEBUDJET) AS 'GRANDTOTAL'
    FROM TABLE 
    GROUP BY BRANCH

2)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(months) 
                    from #TEMP1
                    group by months
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT branch,' + @cols + ',tiot from 
             (
                select *,sum(BUDGET)over(partition by branch) as tiot
                from #TEMP1
            ) x
            pivot 
            (
                sum(BUDGET)
                for months in (' + @cols + ')
            ) p '

exec(@query);

- Chanukya

---

“Grand total” 行怎么样？ - sagi

是的，我无法做到:/ 你能帮我吗？ - Sanya Zahid

每个月你需要计算... @SanyaZahid - Chanukya

你能检查一下动态透视表吗？@SanyaZahid - Chanukya

它仍然不会填充总行。 - sagi

Answer 2

您可以使用条件聚合：

SELECT t.branch,
       SUM(CASE WHEN t.months = 'JAN' THEN t.[Parts Revenue Budget] ELSE 0 END) as JAN,
       SUM(CASE WHEN t.months = 'FEB,' THEN t.[Parts Revenue Budget] ELSE 0 END) as FEB
       ... As many as you need
       SUM(t.[Parts Revenue Budget]) as grandtotal
FROM #Temp1
GROUP BY t.branch
UNION ALL
SELECT 'Grand Total',
        SUM(CASE WHEN t.months = 'JAN' THEN t.[Parts Revenue Budget] ELSE 0 END) as JAN,
        SUM(CASE WHEN t.months = 'FEB' THEN t.[Parts Revenue Budget] ELSE 0 END) as FEB,
       ... As many as you need
       SUM(t.[Parts Revenue Budget]) as grandtotal
FROM #Temp1 t

- sagi

Answer 3

我尝试了以下方法：

CREATE TABLE #TEMP1(BRANCH VARCHAR(100),MONTHS VARCHAR(100),BUDGET varchar(100))

INSERT INTO #TEMP1 VALUES ('CPD','APR','801375')
INSERT INTO #TEMP1 VALUES ('Sabzi Mandi','APR','1033697')
INSERT INTO #TEMP1 VALUES ('DHA','APR','2119835')
INSERT INTO #TEMP1 VALUES ('Quidabad','APR','2100042')
INSERT INTO #TEMP1 VALUES ('Sukkar','AUG','44377320')
INSERT INTO #TEMP1 VALUES ('Baghbanpura','AUG','2726114')
INSERT INTO #TEMP1 VALUES ('Multan','AUG','6068565')


select *,coalesce(CAST(AUG AS NUMERIC(10,2)),0)+coalesce(CAST(apr AS NUMERIC(10,2)),0) as 'grand total'
from 
(select branch , months ,budget from #temp1) as s
pivot 
(MAX(BUDGET) for months in (APR,AUG)) AS PVT

- Ranjana Ghimire

Answer 4

 CREATE TABLE TEMP1(BRANCH VARCHAR(100),MONTHS VARCHAR(100),BUDGET FLOAT(126));

INSERT INTO TEMP1 VALUES ('ABC','AUG','1000');
INSERT INTO TEMP1 VALUES ('PQR','APR','2000');
INSERT INTO TEMP1 VALUES ('XYZ','AUG','1000');



SELECT
    BRANCH,
    APR,
    AUG,
    (NVL(APR,'0')+NVL(AUG,'0')) AS GRANDTOTAL
    FROM
    (SELECT
    *
    FROM
      (
        SELECT
          BRANCH,
          MONTHS,
          BUDGET
        FROM
          TEMP1
      )
      pivot ( MIN(BUDGET) FOR MONTHS IN ('APR' AS APR,'AUG' AS AUG)))
      UNION ALL
      SELECT 'GRAND TOTAL' AS BRANCH,SUM(APR) AS APR, SUM(AUG) AS AUG, SUM(GRANDTOTAL) AS GRANDTOTAL 
      FROM 
      (SELECT
    BRANCH,
    APR,
    AUG,
    (NVL(APR,'0')+NVL(AUG,'0')) AS GRANDTOTAL
    FROM
    (SELECT
    *
    FROM
      (
        SELECT
          BRANCH,
          MONTHS,
          BUDGET
        FROM
          TEMP1
      )
      pivot ( MIN(BUDGET) FOR MONTHS IN ('APR' AS APR,'AUG' AS AUG) )));

- Singh

---

嗨，@Sanya Zahid，你也可以尝试以上查询。 - Singh