SQL SERVER中 PIVOT和UNPIVOT的用法【分组统计】

USE LocalAppDB 
GO
---Use aggregate function
SELECT C.customerid, city,
CASE
WHEN COUNT(orderid) = 0 THEN 'no_orders'
WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders'
WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders'
END AS category
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid
GROUP BY C.customerid, city;
---use pivot key word: Find city [no_orders]/[upto_two_orders]/[more_than_two_orders]'s CustomerID Count
SELECT city,[no_orders],[upto_two_orders],[more_than_two_orders]
FROM 
(
SELECT C.[customerid], [city],
(CASE WHEN COUNT(orderid) = 0 THEN 'no_orders'
WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders'
WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders'
END) AS [category]
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid
GROUP BY C.customerid, city
) p
PIVOT
(
count(p.[customerid]) FOR p.category
IN ([no_orders],[upto_two_orders],[more_than_two_orders])
) AS pvt
---Can Use this SQL Statement To Replace PIVOT(Just Can Use After SQL 2005 Version ) Key Word
SELECT city,
COUNT(CASE WHEN category = 'no_orders'
THEN customerid END) AS [no_orders],
COUNT(CASE WHEN category = 'upto_two_orders'
THEN customerid END) AS [upto_two_orders],
COUNT(CASE WHEN category = 'more_than_two_orders'
THEN customerid END) AS [more_than_two_orders]
FROM (SELECT C.customerid, city,
CASE
WHEN COUNT(orderid) = 0 THEN 'no_orders'
WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders'
WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders'
END AS category
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid
GROUP BY C.customerid, city) AS D
GROUP BY city;
---Now Test the UNPIVOT Key Word
SELECT city,[no_orders],[upto_two_orders],[more_than_two_orders] INTO dbo.PivotedCategories
FROM 
(
SELECT C.[customerid], [city],
(CASE WHEN COUNT(orderid) = 0 THEN 'no_orders'
WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders'
WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders'
END) AS [category]
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid
GROUP BY C.customerid, city
) p
PIVOT
(
count(p.[customerid]) FOR p.category
IN ([no_orders],[upto_two_orders],[more_than_two_orders])
) AS pvt
---
UPDATE dbo.PivotedCategories
SET no_orders = NULL, upto_two_orders = 3
WHERE city = 'Madrid';
SELECT * FROM PivotedCategories
---
SELECT city, category, num_custs
FROM dbo.PivotedCategories
UNPIVOT
(	num_custs FOR
category IN([no_orders],
[upto_two_orders],
[more_than_two_orders])
) AS UNPvt
---Drop the Test Table
DROP TABLE dbo.PivotedCategories;


---This Dome Test OVER Key Word 
SELECT orderid, customerid,
COUNT(*) OVER(PARTITION BY customerid) AS num_orders
FROM dbo.Orders
WHERE customerid IS NOT NULL
AND orderid % 2 = 1;
---Use Aggregate Function
SELECT orderid, customerid,
COUNT(*) AS num_orders
FROM dbo.Orders
WHERE customerid IS NOT NULL
AND orderid % 2 = 1
GROUP BY orderid, customerid
----
SELECT seqno,username,ROW_NUMBER() OVER(ORDER BY seqno DESC) AS [Sseqno],password,enabled
FROM dbo.aduser
ORDER BY seqno
----
SELECT seqno,username,ROW_NUMBER() OVER(PARTITION BY username ORDER BY seqno DESC) AS [Sseqno],password,enabled
FROM dbo.aduser
ORDER BY seqno
--
SELECT seqno,username,COUNT(*) OVER(PARTITION BY username) AS [CT],password,enabled
FROM dbo.aduser
ORDER BY seqno
---
SELECT customerid FROM dbo.Customers
EXCEPT
SELECT customerid FROM dbo.Orders;
---
SELECT customerid FROM dbo.Customers
UNION 
SELECT customerid FROM dbo.Orders;

 

　很悲催，之前写的SQL 相关文档人间蒸发鸟，又得慢慢积累，囧~　

转载于:https://www.cnblogs.com/dowork-perfect/p/3509837.html