September 8, 2016 at 11:51 am
I have an item with 4 units of measure and each one has a different size. I want to get the UOMs as column names with the size listed below so that I have only one line for the result. I have tried a pivot but I get NULL values. Here is what I have so far.
CREATE TABLE test_uom (item_id varchar(10), unit_of_measure varchar(5), unit_size decimal(8,0))
INSERT INTO test_uom (item_id, unit_of_measure, unit_size)
VALUES ('WIRE', 'CTN', 2000),
('WIRE', 'RL', 500),
('WIRE', 'FT', 1),
('WIRE', 'M', 1000)
SELECT item_id
, UOM
, Size
FROM
(SELECT item_id
, unit_of_measure
, unit_size
FROM test_uom
) A
PIVOT (
MIN(unit_size) FOR unit_of_measure IN ([UOM], [Size])
) AS Pvt
September 8, 2016 at 12:11 pm
Try the following instead.
SELECT item_id
, CTN
, RL
, FT
, M
FROM
(SELECT item_id
, unit_of_measure
, unit_size
FROM #test_uom
) A
PIVOT (
MIN(unit_size) FOR unit_of_measure IN ([CTN], [RL], [FT], [M])
) AS Pvt
You were providing the original column names in the pivot when you need to provide the values from the original columns that you want to become the new columns.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 8, 2016 at 12:39 pm
That works Drew. So if I need to expand this for more items and there are additional UOMs for them (PLT, BX, etc.), I assume I will need to add those values into the IN section of the pivot. Thanks for the guidance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply