How to transpose mysql table data using mysql query?

Question

I am working on a mySQL data table. As per the first image attached.

My desired result is that: I would like to display the table where the Table column will be transposed to the left and the data also transposed as per the image below.

Hope to get some advise on how to achieve this using a mysql query?

In addition i would also like to further acheive to outcome.

1) What should i add to my mysql query so that i can set PropertyID to "Property ID" and PropertyName to "Property Name" in the transposed table?

2) What should i add to the query if i only want to show the transposed data of only a particular PropertyId?

Mysql data table:

Expected Transposed data:

Maybe this will help https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql — FanoFN, Dec 31 '19 at 07:25
`What should i add to my mysql query so that i can set PropertyID to "Property ID" and PropertyName to "Property Name" in the transposed table` For this you can use alias (as) . Check Here: http://www.mysqltutorial.org/mysql-alias/ — Amanjot Kaur, Dec 31 '19 at 07:40
`What should i add to the query if i only want to show the transposed data of only a particular PropertyId?` For this, you should use `where` ... example.. `select * from abc where id=1` Read Here: http://www.mysqltutorial.org/mysql-where/ — Amanjot Kaur, Dec 31 '19 at 07:41

score 0 · Answer 1 · answered Dec 31 '19 at 13:34

The format you want is quite arcane and definitely not SQL-ish. You can accomplish it with conditional aggregation:

select cols.which,
       (case when propertyId <> 1 then NULL
             when cols.which = 'PropertyId' then propertyId 
             when cols.which = 'PropertyName' then propertyName
             . . .
        end) as prop_1,
       (case when propertyId <> 2 then NULL
             when cols.which = 'PropertyId' then propertyId 
             when cols.which = 'PropertyName' then propertyName
             . . .
        end) as prop_2,
from t cross join
     (select 'PropertyId' as which union all
      select 'PropertyName' as which union all
      select 'PropertyAddress' as which union all
      . . .
     ) cols
group by cols.which

How to transpose mysql table data using mysql query?

1 Answers1