1

I am trying to unpivot multiple groups of columns with the same attribute but multiple value columns. Say there are 2 products and 3 customers.

enter image description here

I am trying to get a transformed table with 1 attribute(customer: customer1, customer2 and customer3) and 2 values (product1 and product2)

enter image description here

I tried spliting it into 2 tables and then unpivot each table and finally join both the tables. I believe this is an unreasonable approach.

I have also done using multiple selects with union all. The place where I am getting stuck is how will I fill the customer column as all the fields are numerical values and the customer column which is to be formed is categorical.

SELECT ID-1, ID-2, ID-3, product1_customer1 AS customer1, product1_customer2 AS customer2, product1_customer3 AS customer3
FROM table
UNION ALL
SELECT ID-1, ID-2, ID-3, product2_customer1, product2_customer2, product2_customer3
FROM table

How can I get the product column? Can you please point out in the direction where I'm going wrong?

Jishnu Dantu
  • 33
  • 5
  • Possible duplicate of [MySQL - turn table into different table](https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table) – Shadow May 14 '19 at 00:32
  • I dont believe so I tried that, although I am unsure to as what to do. Can you please point me in the right direction. Thanks. – Jishnu Dantu May 14 '19 at 00:50
  • You need to use a series of selects combined by unions to do the unpivot as demonstrated by the answer to the duplicate question. – Shadow May 14 '19 at 07:26
  • Hi @Shadow, I had actually done that. I am editing the question for better understanding. I believe I may have misstated the question entirely. Sorry for that – Jishnu Dantu May 14 '19 at 14:37
  • `[my_table]` - this syntax is used in ms access and ms sql server, not in MySQL. Are you sure you use MySQL in the first place? Also, is the product name stored in the column name of the original table? – Shadow May 14 '19 at 16:52
  • @Shadow Sorry, my bad. Just wanted to represent a table name. Yes, the product name is stored in the column name eg. product1 is in product1_customer1 – Jishnu Dantu May 14 '19 at 17:43
  • Then you either need to hard code the product names or you have to generate the sql statement dynamically using information_schema. – Shadow May 14 '19 at 18:47
  • @Shadow Thanks. I went for the hard coded way. – Jishnu Dantu May 14 '19 at 21:31

0 Answers0