SQL transform table query

Question

I got a problem transforming a table that looks like this

PropertyName | PropertyValue --------------------------------- color red color blue size big size small

into this:

Color | Size --------------------------------- red big red small blue big blue small

How can I achieve this? Thanks in advance for any help.

Martin Smith · Accepted Answer · 2010-09-15 09:50:36Z

1

You want every permutation of colours and sizes?

SELECT color, size FROM ( select distinct PropertyValue AS color from YourTable where PropertyName = 'color' ) T1 CROSS JOIN ( select distinct PropertyValue AS size from YourTable where PropertyName = 'size' ) T2

answered Sep 15, 2010 at 9:50

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

4 Comments

fancyPants Over a year ago

Okay, that`s a start. Thanks very much. But is there a way, to do it automatically for all PropertyNames and -Values? I mean without having to write a cross join for each propertyname?

Martin Smith Over a year ago

Not in standard SQL. Can you tag your question with the RDBMS you are using.

fancyPants Over a year ago

Then I guess I have to live with it. Thanks again.

Martin Smith Over a year ago

@tombom - For SQL server you would need to use dynamic SQL to create a string similar to the above that you then execute.

Collectives™ on Stack Overflow

SQL transform table query

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related