CODEWITHSUNDEEP
mysql
Shujaatali
Shujaatali

Reputation: 131

mysql convert column into row

i have a view that is created form multiple tables view structure is

id     col1     col2     col3     col4

101     A     B     B     NULL

102     C     F     A     G

and so on (this is sample)

i want to create another view that look like

ID     col_name     value

101     col1     A

101     col2     B

101     col3     B

101     col4     NULL

102     col1     C

102     col2     F

102     col3     A

102     col4     G

and so on

can any one help plzzz

Upvotes: 2

Views: 9739

Answers (2)

Arion
Arion

Reputation: 31239

Maybe something like this:

SELECT id,'col1' AS colName, col1 AS value FROM table1
UNION ALL
SELECT id,'col2' AS colName, col2 AS value FROM table1
UNION ALL
SELECT id,'col3' AS colName, col3 AS value FROM table1
UNION ALL
SELECT id,'col4' AS colName, col4 AS value FROM table1

Upvotes: 7

gbn
gbn

Reputation: 432210

CREATE VIEW foo
AS
SELECT id, 'col1' AS col_name, col1 as `Value`
UNION ALL
SELECT id, 'col2', col2
UNION ALL
SELECT id, 'col3', col3
UNION ALL
SELECT id, 'col4', col4;

Upvotes: 0

Related Questions