Search code examples
sqlsplitrowzero

Split Sql Row if Value of a Column is greater than 0

So currently I have a table that has a bunch of fields and 3 cost fields (labor, Parts, Misc). What I want to do is have a the table split this up so that there is only one cost field that actually has data per row. Then have the other cost fields be blank. Example.

Name | Labor |Parts | Misc
test1  800    500     0
test2  0      500     0
test3  700    200     120

to 

Name | Labor |Parts | Misc

test1  800    0       0
test1  0      500     0
test2  0      500     0
test3  700    0       0
test3  0      200     0
test3  0      0     120

I am Fairly new to SQL work and I can't find a good solution to this. Any thoughts would be great.

Thanks

Solution

  • Try this way:

    select Name,  Labor, 0 as Parts,0 as Misc
from Table 
where Labor > 0
union all
select Name,   0 as Labor, Parts,0 as Misc
from Table 
where Parts > 0
union all
select Name,  0 as Labor, 0 as Parts,Misc
from Table 
where Misc > 0