mysql creating rows to column

Question

I want to convert mysql rows to column, i tried but didnt get the required output. Thankyou In Advance. my table query is

First Table

CREATE TABLE `variant_to_group` (
 `variant_to_group_ID` int(11) NOT NULL AUTO_INCREMENT,
 `variant_group_ID` int(11) NOT NULL,
 `variant_ID` int(11) NOT NULL,
 PRIMARY KEY (`variant_to_group_ID`)
)
INSERT INTO `products_to_variants` (`products_to_variants_ID`, `variant_ID`, `variant_value`, `variant_group_ID`) VALUES
(1, 1, 'STD', 1),
(2, 1, '25', 1),
(3, 1, '50', 1),
(4, 1, '75', 1),
(5, 1, '100', 1),
(6, 2, 'USHA', 1),
(7, 2, 'SAM', 1),
(8, 2, 'Getzo', 1);

second table

CREATE TABLE `products_new` (
 `product_ID` int(11) NOT NULL AUTO_INCREMENT,
 `product_name` varchar(100) NOT NULL,
 `brand_ID` int(11) NOT NULL,
 `categories_ID` int(11) NOT NULL,
 `product_variant_group_ID` int(11) NOT NULL,
 PRIMARY KEY (`product_ID`)
) 

INSERT INTO `products_new` (`product_ID`, `product_name`, `brand_ID`, `categories_ID`, `product_variant_group_ID`) VALUES
(1, 'Hero Honda CD 100', 1, 1, 1);

and the desired output required is -

product_name    categories_ID   variant_value_1 variant_value_2
Hero Honda CD 100   1   STD         USHA
Hero Honda CD 100   1   25          USHA
Hero Honda CD 100   1   50          USHA
Hero Honda CD 100   1   75          USHA
Hero Honda CD 100   1   100         USHA
Hero Honda CD 100   1   STD         SAM
Hero Honda CD 100   1   25          SAM
Hero Honda CD 100   1   50          SAM
Hero Honda CD 100   1   75          SAM
Hero Honda CD 100   1   100         SAM
Hero Honda CD 100   1   STD         Getzo
Hero Honda CD 100   1   25          Getzo
Hero Honda CD 100   1   50          Getzo
Hero Honda CD 100   1   75          Getzo
Hero Honda CD 100   1   100         Getzo

i tried some of the search result from google and stackoverflow but i failed to generated above output

You posted the create statement of the table: variant_to_group but the insert statement is for table: products_to_variants. — forpas
– forpas, Commented Mar 25, 2019 at 18:12
It is my mistake, SORRY. this is the create table query for products_to_variant CREATE TABLE products_to_variants ( products_to_variants_ID int(11) NOT NULL, variant_ID int(11) NOT NULL, variant_value varchar(100) NOT NULL, variant_group_ID int(11) NOT NULL ) — Gagandeep
– Gagandeep, Commented Mar 26, 2019 at 1:18

FanoFN · Accepted Answer · 2019-03-26 07:15:28Z

1

Try below query. It should produce the exact output as what you expect it would.

SELECT product_name,categories_ID,v1.variant_value AS "variant_value_1",
v2.variant_value  AS "variant_value_2" 
FROM products_new 

LEFT JOIN
(SELECT * FROM products_to_variants WHERE variant_ID=1) v1 
ON products_new.product_id = products_new.product_variant_group_ID 

LEFT JOIN
(SELECT * FROM products_to_variants WHERE variant_ID=2) v2 
ON products_new.product_id = products_new.product_variant_group_ID
ORDER BY v2.variant_value;

A bit explanation on the query above, I used your products_new table as a reference for the LEFT JOIN. If you look at the query I've provided, I am joining:

products_new.product_id = products_new.product_variant_group_ID

As far as knowing that this joining is valid or not, its up to you but I assume that it's valid. Then there are two sub-queries that I assigned with v1 and v2 aliases. Each of those sub-query I define value of variant_ID differently. As to why I use LEFT JOIN, it's because that your expected output require the variant_value column side by side differentiated by variant_ID value.

However, this query will only cater to your given example data and expected output. Therefore, if you have other condition(s) you would like to meet from this query, please update your question with extra information.

answered Mar 26, 2019 at 7:15

FanoFN

7,1442 gold badges16 silver badges35 bronze badges

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

4 Comments

Gagandeep Over a year ago

Thank you. This is the required output.

Gagandeep Over a year ago

I want to ask one more thing @tcadidot0, As you know variant value is differentiated by variant id. at present i am having only two variant id (1,2) but it can be increase. So I want to create a Dynamic sql query to create above result having more variants.

Gagandeep Over a year ago

I am sorry, i am asking question in the comment. I update my question if you say.

FanoFN Over a year ago

Thats what I'm afraid of @Gagandeep . I'm sorry but I'm limited to just (old) MySQL 4.1 for now. Perhaps there's a way to do in later MySQL version. I can try with newer version but it will take a while. Hopefully someone will give you other suggestion soon

Alex · Accepted Answer · 2019-03-25 17:48:40Z

0

you need to perform a join to get the output you are looking for

SELECT * FROM `products_new` 
INNER JOIN `variant_to_group`  
ON `products_new`.`variant_group_ID` = `products_new`.`product_variant_group_ID`;

edited Mar 25, 2019 at 17:48

Alex

17.3k1 gold badge31 silver badges52 bronze badges

answered Mar 25, 2019 at 17:42

auwalup

938 bronze badges

Collectives™ on Stack Overflow

mysql creating rows to column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related