Generate several aliases in the same case within a select-CodePudding

I am generating a query in SQL, I need to generate three columns for each condition in the case of the select, but I only know how to do it by creating three cases with the same when. I also need the alias to be the same but when generating the same alias a prefix '_1' and '_2' is added as many times as it is repeated.

This is the query:

select
    distinct Layout,
    case when Layout = 'Midd' then 'mid' end as FIR,
    case when Layout = 'Midd' then 'mid' end as SEC,
    case when Layout = 'Midd' then 'mid' end as THI,
    case when Layout = 'Ser' then null end as FIR,
    case when Layout = 'Ser' then null end as SEC,
    case when Layout = 'Ser' then null end as THI,
    case when Layout = 'Cloud' then ser end as FIR,
    case when Layout = 'Cloud' then mid end as SEC,
    case when Layout = 'Cloud' then clo end as THI
from SUMMARY

This is what I get:

Layout  FIR  SEC  THI  FIR_1  SEC_1  THI_1  FIR_2  SEC_2  THI_2     
Midd    mid  mid  mid  null   null   null   null   null   null
Ser     null null null null   null   null   null   null   null
Cloud   null null null null   null   null   dataS  dataM  dataC

This is what I want to achieve:

Layout  FIR    SEC    THI   
Midd    mid    mid    mid  
Ser     null   null   null 
Cloud   dataS  dataM  dataC

This is the query I want to get but I don't know how to make it work:

    select
        distinct Layout,
        case when Layout = 'Midd' then 'mid' end as FIR, SEC, THI,
        case when Layout = 'Ser' then null end as FIR, SEC, THI,
        case when Layout = 'Cloud' then ser as FIR and mid as SEC and clo as THI
    from SUMMARY

CodePudding user response：

See this fiddle:

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=48e63abb462890293f2fa7b54860bf5d

Also, you should include a fiddle with some sample data and your specific database. It will make it easier for people to answer you. For instance, you reference I.ser, I.mid and I.clo in your query, but I don't really have any way of knowing what those would hold, or how to write the query against whatever is aliased as I here. Instead I used static values for the purposes of the demo.

You need to make separate CASE statements, one for each column you intend to produce, and then add multiple WHEN conditions to each:

select
    distinct Layout,
    case when Layout = 'Midd'  then  'mid'
         when Layout = 'Ser'   then   null
         when Layout = 'Cloud' then   I.ser end as FIR,
    case when Layout = 'Midd'  then  'mid'
         when Layout = 'Cloud' then   I.mid
         when Layout = 'Ser'   then   null end as SEC,
    case when Layout = 'Midd'  then  'mid'
         when Layout = 'Ser'   then   null
         when Layout = 'Cloud' then   I.clo end as THI
from SUMMARY