Question 254270 in Mysql Question posted in
The official MySQL documentation can be found here.

Convert columns into rows in mysql

I have two tables with same structure. One table contains old value and another table contains new value of all columns. I want to get out out as columns as 1 column and old and new value row wise with changed value of columns.

CREATE TABLE test (
    Column1 varchar(100) NULL,
    Column2 varchar(100) NULL,
    Column3 varchar(100) NULL
);

INSERT INTO webtest.test
(Column1, Column2, Column3)
VALUES('1', '2', '3');

CREATE TABLE test_hist (
    Column1 varchar(100) NULL,
    Column2 varchar(100) NULL,
    Column3 varchar(100) NULL
);

INSERT INTO test
(Column1, Column2, Column3)
VALUES('1', '3', '4');

I want output like below:

| Field_Name | Old_Value| | New_Value|
| -------- | -------- |  
| Column2   | 2       |    4
| Column3   | 3       |    5

any help would be highly appreciated. thanks in advance.

I tried to use pivot but that is not available in mysql.

Tags:

2

Answers


  1. 0

    You can use a combination of subqueries and UNION.

    SELECT Field_Name, Old_Value, New_Value
FROM (
    SELECT 'Column1' AS Field_Name,
           (SELECT Column1 FROM test WHERE (SELECT Column1 FROM test_hist) <> Column1 LIMIT 1) AS Old_Value,
           (SELECT Column1 FROM test_hist) AS New_Value
    UNION ALL
    SELECT 'Column2' AS Field_Name,
           (SELECT Column2 FROM test WHERE (SELECT Column2 FROM test_hist) <> Column2 LIMIT 1) AS Old_Value,
           (SELECT Column2 FROM test_hist) AS New_Value
    UNION ALL
    SELECT 'Column3' AS Field_Name,
           (SELECT Column3 FROM test WHERE (SELECT Column3 FROM test_hist) <> Column3 LIMIT 1) AS Old_Value,
           (SELECT Column3 FROM test_hist) AS New_Value
) AS Result
WHERE Old_Value IS NOT NULL;
    Login or Signup to reply.
  2. 0

    How about union of joins?

    select 'column 1' as col_nm, b.column1 as old_val, a.column1 as new_val
  from test a join test_hist b on a.column1 <> b.column1
union all
select 'column 2'          , b.column2           , a.column2
  from test a join test_hist b on a.column2 <> b.column2
union all
select 'column 3'          , b.column3           , a.column3
  from test a join test_hist b on a.column3 <> b.column3;

    For sample data you posted, result is

    col_nm    old_val  new_val
--------  -------  -------
column 2  3        2
column 3  4        3

    Have a look at the fiddle that illustrates it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search