Combine multiple query results into one row and add columns

Question

I have searched extensively for this type of resolution but have not been able to find anything that works.

Here is my data:

Table1

co_num |  item id

L11400  | widget1

L11400  | widget2

L11400  | widget3

L11401  | widget1

L11401  | widget2

I would like to query the table and return the data like this:

co_num | itemid1 | itemid2 | itemid3

L11400 | widget1 | widget2 | widget3

L11401 | widget1 | widget2 | (blank)

Is this possible? I'm using SQL 2008. It does need to be dynamic because the number of items may vary. It would be best if this could possibly be down in a view/query.

Thanks

@Mihai I have and cannot seem to get the syntax right. I'll look again. — cdohn
– cdohn, Commented Sep 10, 2014 at 22:30

M.Ali · Accepted Answer · 2014-09-10 22:42:10Z

1

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

DECLARE @TABLE TABLE(co_num VARCHAR(20),itemid VARCHAR(20))
INSERT INTO @TABLE VALUES
('L11400','widget1'),
('L11400','widget2'),
('L11400','widget3'),
('L11401','widget1'),
('L11401','widget2')

SELECT *
FROM (
    SELECT *
           ,'ItemID' + CAST(ROW_NUMBER() OVER (PARTITION BY co_num 
                                            ORDER BY itemid) AS VARCHAR(10)) Items
    FROM @TABLE
    ) t
PIVOT (MAX(itemid)
       FOR Items 
       IN ([ItemID1],[ItemID2],[ItemID3])
       )p

Results:

| CO_NUM | ITEMID1 | ITEMID2 | ITEMID3 |
|--------|---------|---------|---------|
| L11400 | widget1 | widget2 | widget3 |
| L11401 | widget1 | widget2 |  (null) |

answered Sep 10, 2014 at 22:42

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cdohn Over a year ago

Perfect! My query ended up being a little more complex but this got me where I needed to go. Thank you!

Collectives™ on Stack Overflow

Combine multiple query results into one row and add columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related