How to create columns from selected values

Question

Is it possible to create columns(field's names) from selected values? I created "good" names for columns, but couldn't to create text columns.

selected values

I have this:

|   ID | ProductID | PropName|  PropVal|
----------------------------------------
|    1 |    560    |  Color  |   green |
|    2 |    560    |  Family |Resistors|
|    3 |    560    |  Series |    375  |

I need this:

|   ID | ProductID | Color |  Family  |  Series |
------------------------------------------------
|    1 |     560   | green | Resistors|   375   |

Values *cannot* be used dynamically as column names directly in SQL (this even includes operations like PIVOT). The only way to truly get dynamic column names in SQL is with a dynamic query - but, ick! Is this really the desired goal? Posting some current code may clear up the issue .. — user2864740, Dec 08 '13 at 22:16

Hogan · Answer 1 · 2013-12-09T16:28:18.507

I'm not clear on how you get ID column in your results... I'm going to guess it is Min of ID. If it is something else let me know.

You could do this with a pivot, but here is the non pivot way to do it:

WITH ProdList
(
   SELECT MIN(ID), as ID, ProductID FROM tablename GROUP BY ProductID
)
SELECT P.ID,
       P.ProductID,
       c.PropValue AS 'Color'
       f.PropValue AS 'Family'
       s.PropValue AS 'Series'
FROM ProdList P
JOIN tablename c on c.ProductID = P.ProductID AND PropName = 'Color'
JOIN tablename f on c.ProductID = P.ProductID AND PropName = 'Family'
JOIN tablename s on c.ProductID = P.ProductID AND PropName = 'Series'

You could also do it with a sub-query like this if you don't like the CTE:

SELECT P.ID,
       P.ProductID,
       c.PropValue AS 'Color'
       f.PropValue AS 'Family'
       s.PropValue AS 'Series'
FROM (SELECT MIN(ID), as ID, ProductID FROM tablename GROUP BY ProductID) P
JOIN tablename c on c.ProductID = P.ProductID AND PropName = 'Color'
JOIN tablename f on c.ProductID = P.ProductID AND PropName = 'Family'
JOIN tablename s on c.ProductID = P.ProductID AND PropName = 'Series'

Comment Update

If you have more than 50 PropNames you have two choices -- generate something like the above using dynamic sql (do this if you expect the PropName field to change somewhat dynamically or if you can get sum optimization out of it where some queries don't look for all PropName values) or Just doing what I did by hand... not hard if you have a good editor that lets you cut and past with column selects... actually not even hard if you have to type it out... it would take at most a couple of hours.

@Bronskiy - that is nothing like mine, I'm using a self join. — Hogan, Dec 09 '13 at 03:13

score 1 · Answer 2 · edited May 23 '17 at 12:22

Sounds like you may be trying to PIVOT withou an aggregate. If you give us more info on your schema, data and current query we can give a more specific answer. Until then this link may provide some help Pivot rows to columns without aggregate.

Given the test data you have provided and the output you require get you the desired results:

DECLARE @SampleData AS TABLE(ID INT,  ProductID INT,  PropName VARCHAR(20),   PropVal VARCHAR(20))

INSERT INTO @SampleData  VALUES(1, 560, 'Color', 'green')
INSERT INTO @SampleData  VALUES(2, 560, 'Family', 'Resistors')
INSERT INTO @SampleData  VALUES(3, 560, 'Series', '375')

SELECT MIN(ID) Id, ProductID, MIN(Color) AS Color, MIN(Family) AS Family, MIN(Series) AS Series
FROM ( SELECT * FROM @SampleData) src
PIVOT ( MIN(PropVal) FOR PropName IN(Color,Family,Series)) pvt
GROUP BY ProductID

However, I'm not sure this is the way you want to go, you will probably need to research dynamically generated SQL. If you let us know the reason you are taking this approach we may be able to help you find an alternative solution.

How to create columns from selected values

2 Answers2