mikeclemson mikeclemson - 1 month ago 8
SQL Question

How to convert a "flat" SQL output into columns format?

I appreciate any ideas you might have!

I work with a product that allows custom properties to be made, and it writes them to the database table like this, creating a new row per custom property per computer, not a new column.

ID | Name | Value
------------------
123,Manufacturer,Dell
123,Model,Latitude E5450
456,Manufacturer,HP
456,Model,ProBook 450 G3


For my query result, I want to "collapse" the data result so that it looks like this, giving it "columns" again -- one record per computer.

ID|Manufacturer|Model
123,Dell,Latitude E5450
456,HP,ProBook 450 G3


I tried this approach:

SELECT MainTable.ID,
mtManufacturer.Value as 'Manufacturer',
mtModel.Value as 'Model'
FROM MainTable
JOIN MainTable mtManufacturer ON MainTable.ID=mtManufacturer.ID AND mtManufacturer.Name='Manufacturer'
JOIN MainTable mtModel ON MainTable.ID=mtModel.ID AND mtModel.Name='Model'


However, this gives me inaccurate results, like this:

ID|Manufacturer|Model
123,Dell,Latitude E5450 --good
123,Dell,ProBook 450 G3 --bad
456,HP,Latitude E5450 --bad
456,HP,ProBook 450G3 --good


What did I goof up? Also, I simplified the database -- I only need two of the custom properties, but there are ~180-200 of them.

Answer

Assuming that each ID has exactly one manufacturer and exactly one model, you can do something like the following:

SELECT *
FROM (SELECT ID, NAME, Value FROM Product) up
PIVOT(MAX(Value) FOR NAME IN ([Manufacturer],[Model])) AS pvt
ORDER BY ID
GO

Notice that I use the MAX aggregate function which should still result in a valid result based on the assumption that each ID has exactly one manufacturer and exactly one model. If this assumption isn't true, then the MAX aggregate function will probably not behave as you expect it to.

You can verify all of this by running the following code:

-- Creating Test Table
CREATE TABLE Product(ID VARCHAR(25), Name VARCHAR(20), Value VARCHAR(20))
GO

-- Inserting Data into Table
INSERT INTO Product(ID, Name, Value)
VALUES(123,'Manufacturer','Dell')
INSERT INTO Product(ID, Name, Value)
VALUES(123,'Model','Latitude E5450')
INSERT INTO Product(ID, Name, Value)
VALUES(456,'Manufacturer','HP')
INSERT INTO Product(ID, Name, Value)
VALUES(456,'Model','ProBook 450 G3')
GO

-- Selecting and checking entires in table
SELECT *
FROM Product
GO

-- Pivot Table ordered by ID
SELECT *
FROM (SELECT ID, NAME, Value FROM Product) up
PIVOT(MAX(Value) FOR NAME IN ([Manufacturer],[Model])) AS pvt
ORDER BY ID
GO

-- Clean up database
DROP TABLE Product
GO