Is it possible to "expand" a sql table?

Question

SQL

I have a table like this:

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

Is it possible to bring it to this:

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

PropName can be around 50, so writing them manually is a chore.
Can add everything to an array and select each value from it and create a column? and then fill it with values?

Answer 1

technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx _

Answer 2

There is no simple solution, but the script should not be heavy, i.e. take all unique ProductIDs from the table, select rows from the table for each, create an array where key is PropName and value is PropVal, then write everything to a new table, where you create from the array columns named key and value value, I would write everything to MongoDB

Answer 3

- on the one hand, your desired structure is closer to a document-oriented database than a relational one (maybe with which mongo to experiment),
- on the other hand, if it is psql, then you can use json field (version 9.2, this functionality was expanded in 9.3)
- on the third hand, no one It prevents you from immediately writing to the desired structure by creating a table with 50 fields
. But I don’t know a simple and quick way to take and expand like this. I myself will listen with pleasure to the answer if there is one.

Answer 4

maybe look in the direction of the grouping..