如何在Pivot语句中使用案例创建一个Sql [英] How Do I Create An Sql Using Case In Pivot Statement
问题描述
你好,拜托,我需要在一个月的时间内分销我们卖给他们产品的客户数量。
我的查询是这个,但它不起作用:
选择 *
来自(选择卖方, DATE ,CLIENT
来自 proddta.SALE 其中卖方 ( ' JHON',' MATH',' PETER'))
PIVOT(
count(< span class =code-keyword> DISTINCT (CLIENT)) as QUANTITY_CLIENT FOR < span class =code-keyword> DATE in
( case DATE 01/05/14 和 13/05/14 as 01 end ),
( case DATE 介于 14/05/14 和 22/05/14 as 02 end ),
( case 当 DATE 之间 23/05 / 14 和 30/05/14 as 03 小号pan> end )
);
我怀疑是在IN之后,但也许我我开始写错了。
输出如下
------- NumberOfclientsPeriod_01 | NumberOfClientsPeriod02 | NumberofclientsPeriod03
JHON ------------ 23 ------------ -------------- 24 ---------------------- 20
MATH --- --------- 20 -------------------------- 22 ------------- --------- 21
PETER ------------ 24 ------------------ -------- 21 ---------------------- 22
它会是这样的:
DECLARE @ SALE 表(卖方 VARCHAR ( 30 ),[ DATE ] DATETIME ,CLIENTVARCHAR ( 30 ))
INSERT INTO @ SALE (卖方,[ DATE ],CLIENT)
VALUES (' JHON',' 2014-05-01',' A'),(' JHON',' 2014-05-07',< span class =code-string>' B'),(' JHON',' 2014-05-09' ,' C'),(' JHON',' 2014-05-14',' E'),( ' JHON',' 2014-05-15',' A') ,(' JHON',' < span class =code-string> 2014-05-18',' D'),
(' MATH', ' 2014-05-20',' A'),(' MATH',温泉n> 2014-05-21',' B' ),(' MATH',' 2014-05-22',' C'),(' MATH',' 2014-05-25',' D'),(' MATH',' 2014-05-28',' C'),(' MATH',' 2014-05-28',' C'),
(' PETER',' 2014-05-29',' < span class =code-string> A'),(' PETER', ' 2014-05-29',' B'),(' PETER',' 2014-05-30',' D'),(' PETER',' 2014-05-30', ' D'),(' PETER ,' 2014-05-30',' E'),(' PETER' ,' 2014-05-30',' A')
SELECT *
FROM @ SALE
SELECT 卖方,[ 01 ],[ 02 ],[ 03 ]
FROM (
SELECT 卖方,期间,COUNT( DISTINCT 客户端) AS NOFClients
FROM (
SELECT 卖方,[期间] = CASE WHEN [ DATE ] BETWEEN ' 2014- 05-01' AND ' 2014- 05-13' 那么 ' 01'
WHEN [ DATE ] BETWEEN ' 2014-05-14' AND ' 2014-05-22' 那么 ' 02'
WHEN [ DATE ] BETWEEN ' 2014-05-23' AND ' 2014-05-30' 那么 ' 03'
END ,CLIENT
FROM @ SALE
) AS T
WHERE 卖方 IN (' JHON',' MATH' ,' PETER')
GROUP BY 卖方,[期间]
) AS DT
PIVOT(SUM(NOFClients) FOR [期间] IN ([ 01 ],[ 02 ],[ 03 ])) AS PT
结果:
< pre lang =text> JHON 2014-05-01 00:00:00.000 A
JHON 2014-05-07 00:00:00.000 B
JHON 2014-05-09 00:00:00.000 C
JHON 2014-05-14 00:00:00.000 E
JHON 2014-05-15 00:00:00.000 A
JHON 2014-05-18 00:00:00.000 D
MATH 2014-05-20 00:00:00.000 A
MATH 2014-05-21 00:00:00.000 B
MATH 2014-05-22 00:00:00.000 C
MATH 2014-05-25 00:00:00.000 D
MATH 2014-05-28 00:00:00.000 C
MATH 2014-05-28 00:00:00.000 C
PETER 2014-05-29 00:00:00.000 A
PETER 2014-05-29 00:00:00.0 00 B
PETER 2014-05-30 00:00:00.000 D
PETER 2014-05-30 00:00:00.000 D
PETER 2014-05-30 00:00:00.000 E
PETER 2014-05-30 00:00:00.000 A
JHON 3 3 NULL
MATH NULL 3 2
PETER NULL NULL 4
如需了解更多信息,请参阅:
CASE [ ^ ]
PIVOT和UNPIVOT [ ^ ]
聚合函数(Transact-SQL) [ ^ ]
Hello, Please, I need to separete the quantity of clients we had sell them products by sellers in a period of a month.
The query I have is this but it doesn`t work:
select *
from (select SELLER, DATE, CLIENT
from proddta.SALE where SELLER in ('JHON', 'MATH', 'PETER'))
PIVOT (
count(DISTINCT(CLIENT)) as QUANTITY_CLIENT FOR DATE in
(case when DATE between 01/05/14 and 13/05/14 as 01 end),
(case when DATE between 14/05/14 and 22/05/14 as 02 end),
(case when DATE between 23/05/14 and 30/05/14 as 03 end)
);
My doubt is after the "IN" but perhaps I am writng it wrong from the beggining.
The output would be the following
-------NumberOfclientsPeriod_01|NumberOfClientsPeriod02|NumberofclientsPeriod03
JHON------------23 --------------------------24----------------------20
MATH------------20 --------------------------22----------------------21
PETER------------24--------------------------21----------------------22
It would be something like this:
DECLARE @SALE TABLE (SELLER VARCHAR(30), [DATE] DATETIME, CLIENT VARCHAR(30)) INSERT INTO @SALE (SELLER, [DATE], CLIENT ) VALUES('JHON','2014-05-01','A'), ('JHON','2014-05-07','B'), ('JHON','2014-05-09','C'), ('JHON','2014-05-14','E'), ('JHON','2014-05-15','A'), ('JHON','2014-05-18','D'), ('MATH','2014-05-20','A'), ('MATH','2014-05-21','B'), ('MATH','2014-05-22','C'), ('MATH','2014-05-25','D'), ('MATH','2014-05-28','C'), ('MATH','2014-05-28','C'), ('PETER','2014-05-29','A'), ('PETER','2014-05-29','B'), ('PETER','2014-05-30','D'), ('PETER','2014-05-30','D'), ('PETER','2014-05-30','E'), ('PETER','2014-05-30','A') SELECT * FROM @SALE SELECT SELLER, [01], [02], [03] FROM ( SELECT SELLER, Period, COUNT(DISTINCT CLIENT) AS NOFClients FROM ( SELECT SELLER, [Period] = CASE WHEN [DATE] BETWEEN '2014-05-01' AND '2014-05-13' THEN '01' WHEN [DATE] BETWEEN '2014-05-14' AND '2014-05-22' THEN '02' WHEN [DATE] BETWEEN '2014-05-23' AND '2014-05-30' THEN '03' END, CLIENT FROM @SALE ) AS T WHERE SELLER IN ('JHON', 'MATH', 'PETER') GROUP BY SELLER, [Period] ) AS DT PIVOT(SUM(NOFClients) FOR [Period] IN ([01], [02], [03])) AS PT
Results:
JHON 2014-05-01 00:00:00.000 A JHON 2014-05-07 00:00:00.000 B JHON 2014-05-09 00:00:00.000 C JHON 2014-05-14 00:00:00.000 E JHON 2014-05-15 00:00:00.000 A JHON 2014-05-18 00:00:00.000 D MATH 2014-05-20 00:00:00.000 A MATH 2014-05-21 00:00:00.000 B MATH 2014-05-22 00:00:00.000 C MATH 2014-05-25 00:00:00.000 D MATH 2014-05-28 00:00:00.000 C MATH 2014-05-28 00:00:00.000 C PETER 2014-05-29 00:00:00.000 A PETER 2014-05-29 00:00:00.000 B PETER 2014-05-30 00:00:00.000 D PETER 2014-05-30 00:00:00.000 D PETER 2014-05-30 00:00:00.000 E PETER 2014-05-30 00:00:00.000 A
JHON 3 3 NULL MATH NULL 3 2 PETER NULL NULL 4
For further information, please see:
CASE[^]
PIVOT and UNPIVOT[^]
Aggregate Functions (Transact-SQL)[^]
这篇关于如何在Pivot语句中使用案例创建一个Sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!