Sunday, May 23, 2010

Convert columns to rows

Sometimes you need to convert columns to rows to get data you need.
For example, this query is used to get which customers buy which services.

DECLARE @CustomerService TABLE(CustomerID INT
       , Service1 CHAR(1)
       , Service2 CHAR(1)
       , Service3 CHAR(1)
       )
INSERT INTO @CustomerService VALUES(1,'Y','Y','N')
INSERT INTO @CustomerService VALUES(2,'N','N','Y')
INSERT INTO @CustomerService VALUES(3,'Y','N','N')

SELECT CustomerID, [Service], ServiceValue
FROM 
   (SELECT CustomerID
   , Service1
   , Service2
   , Service3
 FROM @CustomerService) pvt
UNPIVOT
   (ServiceValue FOR [Service] IN 
   (Service1
  , Service2
  , Service3)
) A
WHERE ServiceValue = 'Y'

No comments:

Post a Comment

Oracle Query