I have a table like this..

My question is, how to get the outcome like this without Union clause?

which means, this is not what I am looking for

select taxtype,taxable,taxfree into #tb
from (values('mixed',10,20)) tb(taxtype,taxable,taxfree)

select 'taxable' taxtype,
        taxable,
        0 taxfree
from #tb
union all
select 'taxfree',
        0,
        taxfree
from #tb

drop table if exists #tb

CodePudding user response：

use table values constructor

select v.*
from   #tb t
       cross apply
       (
           values
           ('taxable', taxable, 0),
           ('taxfree', 0, taxfree)
       ) v (taxtype, taxable, taxfree)

CodePudding user response：

Unfortunately, despite of the smart support of Squirrel, I accepted Union as my final solution. Just to add some revision..

select taxtype,taxable,taxfree into #src
from (
        values
        ('taxable',5,0),
        ('taxfree',0,10),
        ('mixed',15,20)
    ) src(taxtype,taxable,taxfree)

select  res.*
from    #src s
        cross apply
        (select *
        from (
               values
               ('taxable', s.taxable, 0, s.taxtype),
               ('taxfree', 0, s.taxfree, s.taxtype)
        ) v (taxtype, taxable, taxfree, origin)
        where not (v.taxable = 0 and v.taxfree = 0)) res

drop table if exists #src