×

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

@Florida

这种？

ultrasonic(ultrasonic)
本文发表在 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
(#8950794@0)
Last Updated: 2014-8-26
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / Require write one SQL only,how? -outlink(outlink); 2014-8-25 {412} (#8948334@0)
    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. :) -verano(Summer); 2014-8-25 (#8948363@0)
    • Try below -budwiser(budwiser); 2014-8-25 {206} (#8948503@0)
      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. -mingyangli(Life is a journey); 2014-9-3 (#8963355@0)
        • 谢谢 -budwiser(budwiser); 2014-9-3 (#8963426@0)
        • You are right. I missed it. It is simpler and better. -ultrasonic(ultrasonic); 2014-9-4 (#8966526@0)
    • pivot, completed code is here -xbhydx(IT 南郭); 2014-8-25 {4080} (#8948510@0)
      本文发表在 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 -outlink(outlink); 2014-8-26 (#8950751@0)
        • 这种？ -ultrasonic(ultrasonic); 2014-8-26 {4503} (#8950794@0)
          本文发表在 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，为什么不让用？ -xbhydx(IT 南郭); 2014-8-27 (#8951543@0)
          • +1 with the pivot solution -kakaka(小胖子); 2014-8-27 (#8951563@0)
          • 用Pivot 短点好看。只pivot 一次的话对performance没影响吧 -ultrasonic(ultrasonic); 2014-8-27 (#8952062@0)
          • 是个面试题。 哈哈。。。 -outlink(outlink); 2014-8-28 (#8956164@0)
          • Reminder: Please do not comment on posts regarding mistakes or errors, unless you are invited. 8.13 13:32 (#8928347@0) -geekcode(文心雕码); 2014-8-28 (#8956320@0)
    • Pivot -ultrasonic(ultrasonic); 2014-8-25 {611} (#8948637@0)
      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. -renjl0810(Virtual Void); 2014-8-27 (#8953557@0)
    • My solution without using pivot -deep_blue(BLUE); 2014-8-29 {626} (#8956727@0)
      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 -outlink(outlink); 2014-8-31 (#8959588@0)
        • No pivot but temp table is allowed? -ultrasonic(ultrasonic); 2014-8-31 (#8959723@0)
          • Using table variable makes code simpler and more readable. It’s no problem to use sub-query instead. -deep_blue(BLUE); 2014-9-2 (#8961531@0)
            • 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. -ultrasonic(ultrasonic); 2014-9-2 (#8961992@0)

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论