Multiple Columns UNPIVOT

-- Create Sample Table.
CREATE TABLE Suppliers
(
Id INT,
Product VARCHAR(500),
Supplier1 VARCHAR(500),
Supplier2 VARCHAR(500),
Supplier3 VARCHAR(500),
City1 VARCHAR(500),
City2 VARCHAR(500),
City3 VARCHAR(500)
)
GO
-- Insert Sample Data.
INSERT INTO Suppliers
SELECT 1, 'Car', 'Tata Motors', 'Bajaj', 'Mahindra', 'Jamshedpur','Pune','Mumbai' UNION ALL
SELECT 2, 'Bike', 'Bajaj', 'Hero Honda', 'Suzuki', 'Pune', 'New Delhi', 'Chandigarh' UNION ALL
SELECT 3, 'Cycle', 'Hercules', 'Hero', 'Atlas', 'Mumbai', 'Banglaore', 'Pune'
GO
-- Load Sample data.
SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3
FROM Suppliers where id = 1
GO
-- Multiple Columns UNPIVOT
SELECT
Id, Product, ROW_NUMBER()OVER(Partition By Id Order By Suppliers) as SuppId,
SupplierName, CityName
FROM
(
SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3 FROM Suppliers where id = 1
) as Main
UNPIVOT
(
SupplierName FOR Suppliers IN (Supplier1, Supplier2, Supplier3) -- Suppliers 에는 컬럼명이 들어간다.
Supplier1, Supplier2, Supplier3
) as Sup
UNPIVOT
(
CityName For Cities IN (City1, City2, City3) -- Cities 에는 컬럼명이 들어간다. City1, City2, City3
) as Ct
WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1) -- Suppliers 와 Cities 의 내용에서 동일한 값 검사.

댓글 없음: