Pivot -ultrasonic(ultrasonic) @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 学科技术讨论 / 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)

@Florida

Pivot

Replies, comments and Discussions:

More Topics