CODEWITHSUNDEEP
sqlms-access
Jérémie L
Jérémie L

Reputation: 371

Turn columns into separate table with column name as values

I'm working on an extraction and I have an issue I can't quite solve.

My extraction looks like this :

+--------+-------+----------+----------+-----+------------+
| ID     | Info1 | Product1 | Product2 | ... | Product300 |
+--------+-------+----------+----------+-----+------------+
| 1      | Paul  | 2        |          |     |            |
+--------+-------+----------+----------+-----+------------+
| 2      | Steve |          | 1        |     |            |
+--------+-------+----------+----------+-----+------------+
| 3      | Mark  | 2        |          |     |            |
+--------+-------+----------+----------+-----+------------+
| ...    |       |          |          |     |            |
+--------+-------+----------+----------+-----+------------+
| 150000 | Felix | 1        |          |     | 2          |
+--------+-------+----------+----------+-----+------------+

Products appear as columns. So it's not very easy to use.

I want to split my table with product as an external table like :

+--------+----------+-------+
| ID     | Product  | Value |
+--------+----------+-------+
| 1      | Product1 | 2     |
+--------+----------+-------+
| 2      | Product2 | 1     |
+--------+----------+-------+
| ...    |          |       |
+--------+----------+-------+
| 150000 | Product1 | 1     |
+--------+----------+-------+

Initially this extraction is in Excel but I moved to Access for this purpose.

I have basic knowledge of SQL but not enough to figure out a solution.

Upvotes: 0

Views: 88

Answers (2)

Andre
Andre

Reputation: 27634

As jarlh wrote, the only SQL solution is this:

INSERT INTO TargetTable (ID, Product, Value)

SELECT ID, 'Product1' AS Product, Product1 AS Value FROM SrcTable WHERE Product1 IS NOT NULL
UNION ALL
SELECT ID, 'Product2' AS Product, Product2 AS Value FROM SrcTable WHERE Product2 IS NOT NULL
UNION ALL
etc.

Maybe split it up into chunks, I'm not sure how well 300 UNION ALLs will work in one query.

Upvotes: 1

Janine
Janine

Reputation: 61

You need to unpivot your table:

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

SELECT ID, Product, Value
FROM 
   (SELECT ID, Product1, Product2, Product3, Product4, Product5
   FROM products) p
UNPIVOT
   (Value FOR Product IN 
      (Product1, Product2, Product3, Product4, Product5)
)AS unpvt;

Upvotes: 0

Related Questions