Pivot Multiple Rows Into Single Row

Posted: April 5, 2011 in SQL Server 2005

You can use the PIVOT function in SQL Server to return multiple rows into a single row as follows:

-- Declare table variable
DECLARE @tbl_test TABLE
       (id      INT IDENTITY(1,1),
        item_id INT,
        code    VARCHAR(20),
        value   VARCHAR(100));

-- Insert into table variable
INSERT INTO @tbl_test (item_id, code, value) values (100, 'A', 'Test A1');
INSERT INTO @tbl_test (item_id, code, value) values (100, 'B', 'Test A2');
INSERT INTO @tbl_test (item_id, code, value) values (200, 'A', 'Test B1');
INSERT INTO @tbl_test (item_id, code, value) values (200, 'B', 'Test B2');
INSERT INTO @tbl_test (item_id, code, value) values (300, 'A', 'Test C1');
INSERT INTO @tbl_test (item_id, code, value) values (300, 'B', 'Test C2');

-- Pivot the results to get multiple rows into a single row based on condition
SELECT item_id, 
       [A] AS code_part_1, 
       [B] AS code_part_2
FROM  (SELECT item_id, 
              code, 
              value
       FROM @tbl_test) AS tbl_result
PIVOT (MAX(value)    FOR code IN ([A], [B])) AS pvt_table;
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