×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

pivot, completed code is here

本文发表在 rolia.net 枫下论坛DECLARE @Sales TABLE
(
[PRODUCT] VARCHAR(20),
[SALES_DATE] DATETIME,
[SALES_AMOUNT] NUMERIC(10,3)
)

INSERT INTO @Sales
VALUES
('Tooth Paste', '2014-01-01', 101.00),
('Tooth Paste', '2014-01-11', 111.00),
('Tooth Paste', '2014-01-21', 121.00),
('Tooth Paste', '2014-02-01', 201.00),
('Tooth Paste', '2014-02-11', 211.00),
('Tooth Paste', '2014-02-21', 221.00),
('Tooth Paste', '2014-03-01', 301.00),
('Tooth Paste', '2014-03-11', 311.00),
('Tooth Paste', '2014-03-21', 321.00),
('Tooth Paste', '2014-04-01', 401.00),
('Tooth Paste', '2014-04-11', 411.00),
('Tooth Paste', '2014-04-21', 421.00),
('Tooth Paste', '2014-05-01', 501.00),
('Tooth Paste', '2014-05-11', 511.00),
('Tooth Paste', '2014-05-21', 521.00),
('Tooth Paste', '2014-06-01', 601.00),
('Tooth Paste', '2014-06-11', 611.00),
('Tooth Paste', '2014-06-21', 621.00),
('Tooth Paste', '2014-07-01', 701.00),
('Tooth Paste', '2014-07-11', 711.00),
('Tooth Paste', '2014-07-21', 721.00),
('Tooth Paste', '2014-08-01', 801.00),
('Tooth Paste', '2014-08-11', 811.00),
('Tooth Paste', '2014-08-21', 821.00),
('Tooth Paste', '2014-09-01', 901.00),
('Tooth Paste', '2014-09-11', 911.00),
('Tooth Paste', '2014-09-21', 921.00),
('Tooth Paste', '2014-10-01', 1001.00),
('Tooth Paste', '2014-10-11', 1011.00),
('Tooth Paste', '2014-10-21', 1021.00),
('Tooth Paste', '2014-11-01', 1101.00),
('Tooth Paste', '2014-11-11', 1111.00),
('Tooth Paste', '2014-11-21', 1121.00),
('Tooth Paste', '2014-12-01', 1201.00),
('Tooth Paste', '2014-12-11', 1211.00),
('Tooth Paste', '2014-12-21', 1221.00),
('Tooth Brush', '2014-01-05', 105.00),
('Tooth Brush', '2014-01-15', 115.00),
('Tooth Brush', '2014-01-25', 125.00),
('Tooth Brush', '2014-02-05', 205.00),
('Tooth Brush', '2014-02-15', 215.00),
('Tooth Brush', '2014-02-25', 225.00),
('Tooth Brush', '2014-03-05', 305.00),
('Tooth Brush', '2014-03-15', 315.00),
('Tooth Brush', '2014-03-25', 325.00),
('Tooth Brush', '2014-04-05', 405.00),
('Tooth Brush', '2014-04-15', 415.00),
('Tooth Brush', '2014-04-25', 425.00),
('Tooth Brush', '2014-05-05', 505.00),
('Tooth Brush', '2014-05-15', 515.00),
('Tooth Brush', '2014-05-25', 525.00),
('Tooth Brush', '2014-06-05', 605.00),
('Tooth Brush', '2014-06-15', 615.00),
('Tooth Brush', '2014-06-25', 625.00),
('Tooth Brush', '2014-07-05', 705.00),
('Tooth Brush', '2014-07-15', 715.00),
('Tooth Brush', '2014-07-25', 725.00),
('Tooth Brush', '2014-08-05', 805.00),
('Tooth Brush', '2014-08-15', 815.00),
('Tooth Brush', '2014-08-25', 825.00),
('Tooth Brush', '2014-09-05', 905.00),
('Tooth Brush', '2014-09-15', 915.00),
('Tooth Brush', '2014-09-25', 925.00),
('Tooth Brush', '2014-10-05', 1005.00),
('Tooth Brush', '2014-10-15', 1015.00),
('Tooth Brush', '2014-10-25', 1025.00),
('Tooth Brush', '2014-11-05', 1105.00),
('Tooth Brush', '2014-11-15', 1115.00),
('Tooth Brush', '2014-11-25', 1125.00),
('Tooth Brush', '2014-12-05', 1205.00),
('Tooth Brush', '2014-12-15', 1215.00),
('Tooth Brush', '2014-12-25', 1225.00);


SELECT
PRODUCT, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC]
FROM
(
SELECT
PRODUCT AS PRODUCT,
UPPER(CONVERT(CHAR(3), DATENAME(MONTH, SALES_DATE))) AS [MONTH],
SUM(SALES_AMOUNT) AS TOTAL_AMOUNT
FROM @SALES
GROUP BY PRODUCT, UPPER(CONVERT(CHAR(3), DATENAME(MONTH, SALES_DATE)))
) AS SRC
PIVOT (SUM(TOTAL_AMOUNT) FOR [MONTH] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC])) AS RESULT

----- OUTPUT
PRODUCT JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
Tooth Brush 345.000 645.000 945.000 1245.000 1545.000 1845.000 2145.000 2445.000 2745.000 3045.000 3345.000 3645.000
Tooth Paste 333.000 633.000 933.000 1233.000 1533.000 1833.000 2133.000 2433.000 2733.000 3033.000 3333.000 3633.000更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / Require write one SQL only,how?
    Question

    You have table

    Product Salesdate Sales Amount
    Toothpaste 1/12/2014 $100,000
    Tooth Brush 1/14/2014 $8,0000

    etc

    You have to create report

    Product Jun Sales Feb Sales ... Dec Sales
    Toothpaste $15,000 $12,000 ....
    Toothbrush $10,000 etc
    • Such a mind-twister! I can't do it. :)
    • Try below
      select product,SUM(case when month(salesdate)=1 then amount else 0 end) as 'Jan sales',
      SUM(case when month(salesdate)=2 then amount else 0 end) as 'Feb sales',
      ......

      from table1
      group by product
      • 遗憾,not too many Rolians can appreciate this clean, simple, and standard complaint solution.
        • 谢谢
        • You are right. I missed it. It is simpler and better.
    • pivot, completed code is here
      本文发表在 rolia.net 枫下论坛DECLARE @Sales TABLE
      (
      [PRODUCT] VARCHAR(20),
      [SALES_DATE] DATETIME,
      [SALES_AMOUNT] NUMERIC(10,3)
      )

      INSERT INTO @Sales
      VALUES
      ('Tooth Paste', '2014-01-01', 101.00),
      ('Tooth Paste', '2014-01-11', 111.00),
      ('Tooth Paste', '2014-01-21', 121.00),
      ('Tooth Paste', '2014-02-01', 201.00),
      ('Tooth Paste', '2014-02-11', 211.00),
      ('Tooth Paste', '2014-02-21', 221.00),
      ('Tooth Paste', '2014-03-01', 301.00),
      ('Tooth Paste', '2014-03-11', 311.00),
      ('Tooth Paste', '2014-03-21', 321.00),
      ('Tooth Paste', '2014-04-01', 401.00),
      ('Tooth Paste', '2014-04-11', 411.00),
      ('Tooth Paste', '2014-04-21', 421.00),
      ('Tooth Paste', '2014-05-01', 501.00),
      ('Tooth Paste', '2014-05-11', 511.00),
      ('Tooth Paste', '2014-05-21', 521.00),
      ('Tooth Paste', '2014-06-01', 601.00),
      ('Tooth Paste', '2014-06-11', 611.00),
      ('Tooth Paste', '2014-06-21', 621.00),
      ('Tooth Paste', '2014-07-01', 701.00),
      ('Tooth Paste', '2014-07-11', 711.00),
      ('Tooth Paste', '2014-07-21', 721.00),
      ('Tooth Paste', '2014-08-01', 801.00),
      ('Tooth Paste', '2014-08-11', 811.00),
      ('Tooth Paste', '2014-08-21', 821.00),
      ('Tooth Paste', '2014-09-01', 901.00),
      ('Tooth Paste', '2014-09-11', 911.00),
      ('Tooth Paste', '2014-09-21', 921.00),
      ('Tooth Paste', '2014-10-01', 1001.00),
      ('Tooth Paste', '2014-10-11', 1011.00),
      ('Tooth Paste', '2014-10-21', 1021.00),
      ('Tooth Paste', '2014-11-01', 1101.00),
      ('Tooth Paste', '2014-11-11', 1111.00),
      ('Tooth Paste', '2014-11-21', 1121.00),
      ('Tooth Paste', '2014-12-01', 1201.00),
      ('Tooth Paste', '2014-12-11', 1211.00),
      ('Tooth Paste', '2014-12-21', 1221.00),
      ('Tooth Brush', '2014-01-05', 105.00),
      ('Tooth Brush', '2014-01-15', 115.00),
      ('Tooth Brush', '2014-01-25', 125.00),
      ('Tooth Brush', '2014-02-05', 205.00),
      ('Tooth Brush', '2014-02-15', 215.00),
      ('Tooth Brush', '2014-02-25', 225.00),
      ('Tooth Brush', '2014-03-05', 305.00),
      ('Tooth Brush', '2014-03-15', 315.00),
      ('Tooth Brush', '2014-03-25', 325.00),
      ('Tooth Brush', '2014-04-05', 405.00),
      ('Tooth Brush', '2014-04-15', 415.00),
      ('Tooth Brush', '2014-04-25', 425.00),
      ('Tooth Brush', '2014-05-05', 505.00),
      ('Tooth Brush', '2014-05-15', 515.00),
      ('Tooth Brush', '2014-05-25', 525.00),
      ('Tooth Brush', '2014-06-05', 605.00),
      ('Tooth Brush', '2014-06-15', 615.00),
      ('Tooth Brush', '2014-06-25', 625.00),
      ('Tooth Brush', '2014-07-05', 705.00),
      ('Tooth Brush', '2014-07-15', 715.00),
      ('Tooth Brush', '2014-07-25', 725.00),
      ('Tooth Brush', '2014-08-05', 805.00),
      ('Tooth Brush', '2014-08-15', 815.00),
      ('Tooth Brush', '2014-08-25', 825.00),
      ('Tooth Brush', '2014-09-05', 905.00),
      ('Tooth Brush', '2014-09-15', 915.00),
      ('Tooth Brush', '2014-09-25', 925.00),
      ('Tooth Brush', '2014-10-05', 1005.00),
      ('Tooth Brush', '2014-10-15', 1015.00),
      ('Tooth Brush', '2014-10-25', 1025.00),
      ('Tooth Brush', '2014-11-05', 1105.00),
      ('Tooth Brush', '2014-11-15', 1115.00),
      ('Tooth Brush', '2014-11-25', 1125.00),
      ('Tooth Brush', '2014-12-05', 1205.00),
      ('Tooth Brush', '2014-12-15', 1215.00),
      ('Tooth Brush', '2014-12-25', 1225.00);


      SELECT
      PRODUCT, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC]
      FROM
      (
      SELECT
      PRODUCT AS PRODUCT,
      UPPER(CONVERT(CHAR(3), DATENAME(MONTH, SALES_DATE))) AS [MONTH],
      SUM(SALES_AMOUNT) AS TOTAL_AMOUNT
      FROM @SALES
      GROUP BY PRODUCT, UPPER(CONVERT(CHAR(3), DATENAME(MONTH, SALES_DATE)))
      ) AS SRC
      PIVOT (SUM(TOTAL_AMOUNT) FOR [MONTH] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC])) AS RESULT

      ----- OUTPUT
      PRODUCT JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
      Tooth Brush 345.000 645.000 945.000 1245.000 1545.000 1845.000 2145.000 2445.000 2745.000 3045.000 3345.000 3645.000
      Tooth Paste 333.000 633.000 933.000 1233.000 1533.000 1833.000 2133.000 2433.000 2733.000 3033.000 3333.000 3633.000更多精彩文章及讨论,请光临枫下论坛 rolia.net
      • 不让用pivot
        • 这种?
          本文发表在 rolia.net 枫下论坛DECLARE @Sales TABLE
          (
          [PRODUCT] VARCHAR(20),
          [SALES_DATE] DATETIME,
          [SALES_AMOUNT] NUMERIC(10,3)
          )

          INSERT INTO @Sales
          VALUES
          ('Tooth Paste', '2014-01-01', 101.00),
          ('Tooth Paste', '2014-01-11', 111.00),
          ('Tooth Paste', '2014-01-21', 121.00),
          ('Tooth Paste', '2014-02-01', 201.00),
          ('Tooth Paste', '2014-02-11', 211.00),
          ('Tooth Paste', '2014-02-21', 221.00),
          ('Tooth Paste', '2014-03-01', 301.00),
          ('Tooth Paste', '2014-03-11', 311.00),
          ('Tooth Paste', '2014-03-21', 321.00),
          ('Tooth Paste', '2014-04-01', 401.00),
          ('Tooth Paste', '2014-04-11', 411.00),
          ('Tooth Paste', '2014-04-21', 421.00),
          ('Tooth Paste', '2014-05-01', 501.00),
          ('Tooth Paste', '2014-05-11', 511.00),
          ('Tooth Paste', '2014-05-21', 521.00),
          ('Tooth Paste', '2014-06-01', 601.00),
          ('Tooth Paste', '2014-06-11', 611.00),
          ('Tooth Paste', '2014-06-21', 621.00),
          ('Tooth Paste', '2014-07-01', 701.00),
          ('Tooth Paste', '2014-07-11', 711.00),
          ('Tooth Paste', '2014-07-21', 721.00),
          ('Tooth Paste', '2014-08-01', 801.00),
          ('Tooth Paste', '2014-08-11', 811.00),
          ('Tooth Paste', '2014-08-21', 821.00),
          ('Tooth Paste', '2014-09-01', 901.00),
          ('Tooth Paste', '2014-09-11', 911.00),
          ('Tooth Paste', '2014-09-21', 921.00),
          ('Tooth Paste', '2014-10-01', 1001.00),
          ('Tooth Paste', '2014-10-11', 1011.00),
          ('Tooth Paste', '2014-10-21', 1021.00),
          ('Tooth Paste', '2014-11-01', 1101.00),
          ('Tooth Paste', '2014-11-11', 1111.00),
          ('Tooth Paste', '2014-11-21', 1121.00),
          ('Tooth Paste', '2014-12-01', 1201.00),
          ('Tooth Paste', '2014-12-11', 1211.00),
          ('Tooth Paste', '2014-12-21', 1221.00),
          ('Tooth Brush', '2014-01-05', 105.00),
          ('Tooth Brush', '2014-01-15', 115.00),
          ('Tooth Brush', '2014-01-25', 125.00),
          ('Tooth Brush', '2014-02-05', 205.00),
          ('Tooth Brush', '2014-02-15', 215.00),
          ('Tooth Brush', '2014-02-25', 225.00),
          ('Tooth Brush', '2014-03-05', 305.00),
          ('Tooth Brush', '2014-03-15', 315.00),
          ('Tooth Brush', '2014-03-25', 325.00),
          ('Tooth Brush', '2014-04-05', 405.00),
          ('Tooth Brush', '2014-04-15', 415.00),
          ('Tooth Brush', '2014-04-25', 425.00),
          ('Tooth Brush', '2014-05-05', 505.00),
          ('Tooth Brush', '2014-05-15', 515.00),
          ('Tooth Brush', '2014-05-25', 525.00),
          ('Tooth Brush', '2014-06-05', 605.00),
          ('Tooth Brush', '2014-06-15', 615.00),
          ('Tooth Brush', '2014-06-25', 625.00),
          ('Tooth Brush', '2014-07-05', 705.00),
          ('Tooth Brush', '2014-07-15', 715.00),
          ('Tooth Brush', '2014-07-25', 725.00),
          ('Tooth Brush', '2014-08-05', 805.00),
          ('Tooth Brush', '2014-08-15', 815.00),
          ('Tooth Brush', '2014-08-25', 825.00),
          ('Tooth Brush', '2014-09-05', 905.00),
          ('Tooth Brush', '2014-09-15', 915.00),
          ('Tooth Brush', '2014-09-25', 925.00),
          ('Tooth Brush', '2014-10-05', 1005.00),
          ('Tooth Brush', '2014-10-15', 1015.00),
          ('Tooth Brush', '2014-10-25', 1025.00),
          ('Tooth Brush', '2014-11-05', 1105.00),
          ('Tooth Brush', '2014-11-15', 1115.00),
          ('Tooth Brush', '2014-11-25', 1125.00),
          ('Tooth Brush', '2014-12-05', 1205.00),
          ('Tooth Brush', '2014-12-15', 1215.00),
          ('Tooth Brush', '2014-12-25', 1225.00);

          select PRODUCT,SUM(JAN) AS JAN,SUM(FEB) AS FEB,SUM(MAR) AS MAR,SUM(APR) AS APR,SUM(MAY) AS MAY,SUM(JUN) AS JUN,SUM(JUL) AS JUL,SUM(AUG) AS AUG,SUM(SEP) AS SEP,SUM(OCT) AS OCT,SUM(NOV) AS NOV,SUM([DEC]) AS [DEC]
          from(
          select product,
          MAX(Case month([SALES_DATE]) When 1 Then [SALES_AMOUNT] End) JAN,
          MAX(Case month([SALES_DATE]) When 2 Then [SALES_AMOUNT] end) FEB,
          MAX(Case month([SALES_DATE]) When 3 Then [SALES_AMOUNT] end) MAR,
          MAX(Case month([SALES_DATE]) When 4 Then [SALES_AMOUNT] end) APR,
          MAX(Case month([SALES_DATE]) When 5 Then [SALES_AMOUNT] end) MAY,
          MAX(Case month([SALES_DATE]) When 6 Then [SALES_AMOUNT] end) JUN,
          MAX(Case month([SALES_DATE]) When 7 Then [SALES_AMOUNT] end) JUL,
          MAX(Case month([SALES_DATE]) When 8 Then [SALES_AMOUNT] end) AUG,
          MAX(Case month([SALES_DATE]) When 9 Then [SALES_AMOUNT] end) SEP,
          MAX(Case month([SALES_DATE]) When 10 Then [SALES_AMOUNT] end) OCT,
          MAX(Case month([SALES_DATE]) When 11 Then [SALES_AMOUNT] end) NOV,
          MAX(Case month([SALES_DATE]) When 12 Then [SALES_AMOUNT] end) [DEC]
          from @SALES
          group by Product,[SALES_AMOUNT]
          ) as List
          group by product更多精彩文章及讨论,请光临枫下论坛 rolia.net
        • 不让用pivot?大概是出题目的人不懂吧。实际开发中,只要能解决问题、不影响performance,为什么不让用?
          • +1 with the pivot solution
          • 用Pivot 短点好看。只pivot 一次的话对performance没影响吧
          • 是个面试题。 哈哈。。。
          • Reminder: Please do not comment on posts regarding mistakes or errors, unless you are invited. 8.13 13:32 (#8928347@0)
    • Pivot
      CREATE TABLE #P

      ( Product varchar(500), Salesdate datetime, Amount varchar(200)
      )
      insert into #P( Product , Salesdate , Amount)
      values('Toothpaste','2014-01-02','100.0'),
      ('Tooth Brush','2014-01-02','10.0'),
      ('Toothpaste','2014-01-02','50.0'),
      ('Tooth Brush','2014-02-02','200.0')

      select Product,January,February from (
      select Product, DateName(month,DateAdd(month,month(Salesdate),0)-1) as Months,sum(convert(float,Amount)) as Amounts from #P
      group by Product, month(Salesdate)
      ) as DT
      PIVOT(MAX(Amounts) for Months IN (January,February)) as P1

      drop table #P
    • Learning R. This can be easily done using reshape.
    • My solution without using pivot
      DECLARE @temp TABLE (Product VARCHAR(50), [Month] INT, SalesByMonth MONEY)

      INSERT @temp
      SELECT DISTINCT Product, MONTH(SalesDate) [Month], SUM(SalesAmount) OVER (PARTITION BY Product, MONTH(SalesDate)) SalesByMonth FROM YourTableName WHERE
      YEAR(SalesDate) = Year

      SELECT Product,
      ISNULL((SELECT SalesByMonth FROM @temp WHERE [Month] = 1 AND Product = T.Product), 0) AS Jan,
      ISNULL((SELECT SalesByMonth FROM @temp WHERE [Month] = 2 AND Product = T.Product), 0) AS Feb,
      ISNULL((SELECT SalesByMonth FROM @temp WHERE [Month] = 3 AND Product = T.Product), 0) AS Mar,
      ...,
      ...

      FROM @temp T GROUP BY T.Product
      • cool
        • No pivot but temp table is allowed?
          • Using table variable makes code simpler and more readable. It’s no problem to use sub-query instead.
            • That is not what I asked. I found that it is odd that it doesn't allow pivot but temp table is ok. Use of temp table makes the question pointless to me.