Sqlserver : Five methods converting rows to columns

Source : http://sqlserveradvisor.blogspot.in/2009/03/sql-server-convert-rows-to-columns.html

 

Post with five methods for converting rows to columns.

From the classic ‘CASE’ construction to the newer PIVOT and ROW_NUMBER() OVER (ORDER BY) functions.

Be careful!
Some methods presented here can have a severe negative impact on performance. Especially the in-line function with a cursor should be used with the utmost precaution.Check the query plan and the statistics i/o (see this post) of your queries before putting the code into your production environment!

1. use a CASE statement

SELECT DATEPART(YEAR,orderdate),
SUM(CASE WHEN DATEPART(q,orderdate) = 1 THEN orderamount ELSE 0 END) AS Qtr1,
SUM(CASE WHEN DATEPART(q,orderdate) = 2 THEN orderamount ELSE 0 END) AS Qtr2,
SUM(CASE WHEN DATEPART(q,orderdate) = 3 THEN orderamount ELSE 0 END) AS Qtr3,
SUM(CASE WHEN DATEPART(q,orderdate) = 4 THEN orderamount ELSE 0 END) AS Qtr4,
SUM(orderamount) AS Total
FROM Orders
— additional where clause goes here…
GROUP BY DATEPART(YEAR,orderdate)

2. use the COALESCE function

DECLARE @AllValues VARCHAR(4000)

SELECT @AllValues = COALESCE(@AllValues + ‘,’, ”) + HandlingCode
FROM OrdersDetails
WHERE OrderNumber = @OrderNumber

3. use ROW_NUMBER() OVER (ORDER BY)

SELECT OrderNumber, OrderDate,
–get the special handling codes and show them as columns, max of 3 (agreed by users)
(SELECT HandlingCode
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
FROM OrdersDetails
WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
) HandlingCode
WHERE ROWNUMBER = 1) HandlingCode1,
(SELECT HandlingCode
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
FROM OrdersDetails
WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
) HandlingCode
WHERE ROWNUMBER = 2) HandlingCode2,
(SELECT HandlingCode
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
FROM OrdersDetails
WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
) HandlingCode
WHERE ROWNUMBER = 3) HandlingCode3
FROM Orders
WHERE OrderNumber = @OrderNumber

4. Use an inline function

SELECT OrderNumber, OrderDate, fn_GetHandlingCodes (OrderNumber)
FROM Orders
WHERE OrderNumber = @OrderNumber

CREATE FUNCTION dbo.fn_GetHandlingCodes (@OrderNumber INT)
RETURNS VARCHAR(1200)
AS
BEGIN
DECLARE @HandlingCode VARCHAR(20)
DECLARE @ReturnValue  VARCHAR(4000)

— use that fastest cursor methods: local fast_forward
DECLARE code_cursor CURSOR LOCAL fast_forward FOR
SELECT HandlingCode
FROM OrdersDetails
WHERE OrderNumber = @OrderNumber
AND NOT HandlingCode IS NULL — filled

SET @ReturnValue = ”  — set to non null

OPEN code_cursor
FETCH NEXT FROM code_cursor  INTO @HandlingCode
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ReturnValue = @ReturnValue + @HandlingCode + ‘, ‘

IF LEN (@ReturnValue) > 1000 BREAK — avoid overflow

FETCH NEXT FROM code_cursor INTO @HandlingCode
END

CLOSE code_cursor
DEALLOCATE code_cursor

— remove last delimiter
IF LEN(@ReturnValue) > 1 SET @ReturnValue = SUBSTRING(@ReturnValue,1,LEN(@ReturnValue)-2)

RETURN @ReturnValue
END

5. Use a pivot

USE AdventureWorks
GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: