CODEWITHSUNDEEP
mysqlsql
Robert
Robert

Reputation: 61

Query in sql -- pivot or any other way

Need your help in query for the following scenario.

Table1 

Name -ID1  - ID2 - ID3  
A1  - NULL - 11 - 12  
A2  -  14  - NULL -11

The above table need to translated like below

Table2 

Name - ID  
A1  - 11  
A1  - 12  
A2 - 14  
A2 - 11

The table2 would be the output based on table 1. All the null values would not be considered. What is the best way to do this.

Can anyone help me with a query on how to do this. Thanks a lot

Upvotes: 0

Views: 39

Answers (2)

Namratha
Namratha

Reputation: 32

You may use UNPIVOT:

SELECT name, ID 
FROM ( SELECT name, ID1, ID2, ID3 FROM table1)p
UNPIVOT
     (ID FOR ID_value IN (ID1, ID2, ID3)) AS unpvt;

This will eliminate the nulls automatically, and will give you the output in the pivoted format.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use UNION ALL :

SELECT t.*
FROM
(
    SELECT name, id1 AS id FROM table1
    UNION ALL
    SELECT name, id2 FROM table1
    UNION ALL
    SELECT name, id3 FROM table1
) t
WHERE id IS NOT NULL
ORDER BY name;

Upvotes: 3

Related Questions