Rows To Columns with Condition from single table on MariaDB Server-CodePudding

I have table on like this :

id_biling  | disc_A  | disc_B | disc_C
    1         100        0        0
    2         200       300      400
    3         500        0       600

I want to get result like this :

id_biling  | disc |  Desc
    1        100     Disc_A
    2        200     Disc_A 
    2        300     Disc_B
    2        400     Disc_C
    3        500     Disc_A
    3        600     Disc_C

CodePudding user response：

Here's a solution with SQL server

You can use unpivot.

select *
from   t
UNPIVOT  
   (disc FOR "DESC" IN   
      (disc_A, disc_B, disc_C)  
)AS unpvt;

id_biling	disc	DESC
1	100	disc_A
2	200	disc_A
2	300	disc_B
2	400	disc_C
3	500	disc_A
3	600	disc_C

Fiddle

CodePudding user response：

Problem solved inspirated by MySQL - How to unpivot columns to rows?

(select id_billing, 'DISC_A' DESCR, DISC_A as DISC from tTable where DISC_A > 0 ) 
union all 
(select id_billing, 'DISC_B' DESCR, DISC_B as DISC from tTable where DISC_B > 0 ) 
union all 
(select id_billing, 'DISC_C' DESCR, DISC_C as DISC from tTable where DISC_C > 0 ) 
ORDER BY 1,2;