Using SQL Server, display some columns as additional rows

Question

I have two tables, let's call one Items and one Inventory.

The items table has a column for the item#, and then 3 columns for accessories, ACC1, ACC2 and ACC3. The inventory table has item# and quantity on hand.

I would like to be able to display this as:

item#A Acc1 qtyonhand
item#A Acc2 qtyonhand
item#A Acc3 qtyonhand
item#B Acc1 qtyonhand

So that each line would be the item and a unique accessory. If the Acc1,2,or 3 column is empty, the line should not be shown.

How would I write this in SQL? I've failed in every attempt I've made to get these exact results.

Thanks in advance!

Louis Ricci · Accepted Answer · 2013-08-01 17:05:50Z

1

You just have to use UNPIVOT

BEGIN
DECLARE @tbl1 as TABLE(num INT, a1 VARCHAR(16), a2 VARCHAR(16), a3 VARCHAR(16)) 
DECLARE @tbl2 as TABLE(num INT, qty INT)
INSERT INTO @tbl1 VALUES (1, 'a1', 'b1', 'c1'),(2, null, 'b2', null),(3, 'a3', 'b3', 'c3') 
INSERT INTO @tbl2 VALUES (1, 5), (2, 10), (3, 7)

SELECT num, acc [acc], qty FROM (
    SELECT a.num, a.a1, a.a2, a.a3, b.qty FROM @tbl1 a
    LEFT JOIN @tbl2 b ON a.num = b.num
) t1
UNPIVOT (
    value FOR acc IN ([a1], [a2], [a3])
) u
END

answered Aug 1, 2013 at 17:05

Louis Ricci

21.1k5 gold badges50 silver badges64 bronze badges

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

4 Comments

user2642976 Over a year ago

Thanks a lot for the reply. This almost works for me, and I'm sure I just need to do something minor. Based on what you said, I have the code below.

user2642976 Over a year ago

SELECT ItemNumber, acc [acc], QtyOnHandBK FROM ( SELECT a.itemnumber, a.Accessory1, a.Accessory2, a.Accessory3, b.QtyOnHandBK FROM CIX a LEFT JOIN inventory b ON a.itemnumber = b.itemnumber ) t1 UNPIVOT ( value FOR acc IN ([Accessory1], [accessory2], [accessory3]) ) u

user2642976 Over a year ago

(sorry, im new to commenting)...this code above gives me my itemnumber, then "Accessory1", and then the on hand. I want to see the actual accessory item from a.Accessory1. I tried putting a.accessory1 in the [ ] after value FOR acc IN(, but this threw an error. What do I need to change? Thanks a lot for your help.

Louis Ricci Over a year ago

@user2642976 - You capitalized the A in Accessory1 but not for 2 or 3.

lc. · Accepted Answer · 2013-08-01 17:22:29Z

0

The naive way would be to use a UNION:

SELECT i.[item#], ACC1 AS ACC, [quantity on hand]
FROM items i
INNER JOIN inventory inv ON i.[item#] = inv.[item#]
WHERE ACC1 IS NOT NULL
UNION ALL
SELECT i.[item#], ACC2 AS ACC, [quantity on hand]
FROM items i
INNER JOIN inventory inv ON i.[item#] = inv.[item#]
WHERE ACC2 IS NOT NULL
UNION ALL
SELECT i.[item#], ACC3 AS ACC, [quantity on hand]
FROM items i
INNER JOIN inventory inv ON i.[item#] = inv.[item#]
WHERE ACC3 IS NOT NULL

edited Aug 1, 2013 at 17:22

answered Aug 1, 2013 at 16:49

lc.

117k21 gold badges163 silver badges190 bronze badges

2 Comments

Louis Ricci Over a year ago

This seems like a brute-force approach when there are directives like UNPIVOT msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

user2642976 Over a year ago

Thank you, this worked, but I do want to know more about the UNPIVOT optoon

Collectives™ on Stack Overflow

Using SQL Server, display some columns as additional rows

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related