--=============================================================================
-- Script #6-008
SELECT *
FROM Production.Product
PIVOT ( COUNT(Color)
FOR Color
IN ([Black],[Blue],[Gery],[Multi],[Red],[Silver],[Silver/Black],[White],[Yellow])
) AS Pvt
--=============================================================================
-- Script #6-009
SELECT ProductSubcategoryID
, [Black] AS color_Black
, [Blue] AS color_Blue
, [Gery] AS color_Gery
, [Multi] AS color_Multi
, [Red] AS color_Red
, [Silver] AS color_Silver
, [Silver/Black] AS color_Silver_Black
, [White] AS color_White
, [Yellow] AS color_Yellow
FROM
(SELECT ProductID ,ProductSubcategoryID,Color
FROM Production.Product) AS PvtS
PIVOT ( COUNT(ProductID) --내용(데이터)로 표현되는 값
FOR Color --가로 열로 표현될 데이터 값 지정
IN ([Black],[Blue],[Gery],[Multi],[Red],[Silver],[Silver/Black],[White],[Yellow])
) AS Pvt
--=============================================================================
-- Script #6-010
SELECT ProductSubcategoryID
, SUM(CASE Color WHEN 'Black' THEN 1 ELSE 0 END) AS color_Black
, SUM(CASE Color WHEN 'Blue' THEN 1 ELSE 0 END) AS color_Blue
, SUM(CASE Color WHEN 'Gery' THEN 1 ELSE 0 END) AS color_Gery
, SUM(CASE Color WHEN 'Multi' THEN 1 ELSE 0 END) AS color_Multi
, SUM(CASE Color WHEN 'Red' THEN 1 ELSE 0 END) AS color_Red
, SUM(CASE Color WHEN 'Silver' THEN 1 ELSE 0 END) AS color_Silver
, SUM(CASE Color WHEN 'Silver/Black' THEN 1 ELSE 0 END) AS color_Silver_Black
, SUM(CASE Color WHEN 'White' THEN 1 ELSE 0 END) AS color_White
, SUM(CASE Color WHEN 'Yellow' THEN 1 ELSE 0 END) AS color_Yellow
, SUM(CASE WHEN Color IS NULL THEN 1 ELSE 0 END) AS color_NULL
FROM Production.Product
GROUP BY ProductSubcategoryID
--=============================================================================
-- Script #6-011
SELECT ProductSubcategoryID
, [Black] AS color_Black
, [Blue] AS color_Blue
, [Gery] AS color_Gery
, [Multi] AS color_Multi
, [Red] AS color_Red
, [Silver] AS color_Silver
, [Silver/Black] AS color_Silver_Black
, [White] AS color_White
, [Yellow] AS color_Yellow
INTO Pvt_Temp
FROM
(SELECT ProductID ,ProductSubcategoryID,Color
FROM Production.Product) AS PvtS
PIVOT ( COUNT(ProductID)
FOR Color
IN ([Black],[Blue],[Gery],[Multi],[Red],[Silver],[Silver/Black],[White],[Yellow])
) AS Pvt
--=============================================================================
-- Script #6-012
SELECT ProductSubcategoryID, Color, Clor_Count
FROM
(SELECT *
FROM Pvt_Temp) p
UNPIVOT
(Clor_Count FOR Color
IN ([color_Black],[color_Blue],[color_Gery],[color_Multi],[color_Red]
,[color_Silver],[color_Silver_Black],[color_White],[color_Yellow])
)AS unpvt
--=============================================================================
-- Script #6-013
SELECT SalesOrderID
, CustomerID
, ROW_NUMBER() OVER (ORDER BY SalesOrderID DESC) AS RunningCount
FROM Sales.SalesOrderHeader
WHERE SalesOrderID > 75110
ORDER BY SalesOrderID
--=============================================================================
-- Script #6-014
SELECT SalesOrderID
, CustomerID
, ROW_NUMBER() OVER (ORDER BY CustomerID) AS RunningCount
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (1,2)
ORDER BY CustomerID
--=============================================================================
-- Script #6-015
SELECT SalesOrderID
, CustomerID
, RANK() Over (Order By CustomerID) AS RunningCount
FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN 11020 AND 11025
ORDER BY CustomerID
--=============================================================================
-- Script #6-016
SELECT SalesOrderID
, CustomerID
, DENSE_RANK() Over (Order By CustomerID) AS RunningCount
FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN 11020 AND 11025
ORDER BY CustomerID
--=============================================================================
-- Script #6-017
SELECT SalesOrderID
, CustomerID
, NTILE(4) Over (Order By CustomerID) AS RunningCount
FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN 11020 AND 11025
ORDER BY CustomerID
--=============================================================================
-- Script #6-018
SELECT SalesOrderID
, CustomerID
, ROW_NUMBER() Over (Order By CustomerID) AS RowNumber
, RANK() Over (Order By CustomerID) AS Rank
, DENSE_RANK() Over (Order By CustomerID) AS DenseRank
, NTILE(4) Over (Order By CustomerID) AS Ntile
FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN 11020 AND 11025
ORDER BY CustomerID
--=============================================================================
-- Script #6-019
SELECT SalesOrderID
, SalesPersonID
, OrderDate
, ROW_NUMBER() OVER (Partition BY SalesPersonID ORDER BY OrderDate) AS OrderRank
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IN (288,284,280)
ORDER BY SalesPersonID DESC
명령어들... 주석좀달아주세요 공부하는데 책에도 딱히.. ㅠㅠㅠ
|
22019, 
1/1101 
