Tell me the solution is such a smart problem: we have the result of the sample

enter image description here

As a result, a line appears where, in two columns of both FixHours and AddHours, there are values ​​that are not equal to zero:

FixHours = 0.5 AddHours = 1.5 CalculatedValue = 16.5

As it can be divided into two, to get the line:

FixHours = 0.5 AddHours = 0 CalculatedValue = 16.5

FixHours = 0 AddHours = 1.5 CalculatedValue = 16.5

The idea was to find only this line, then artificially create the second one with the help of Union. But she failed because Such rows are in the table more than once. In general, any ideas?

    4 answers 4

    If the order is not important, then:

    WITH SourceData AS ( SELECT FixHours, AddHours, CalculatedValue FROM SomeTable --тут ваша выборка ) SELECT FixHours, 0, CalculatedValue FROM SourceData WHERE FixHours <> 0 UNION ALL SELECT 0, AddHours, CalculatedValue FROM SourceData WHERE AddHours <> 0 UNION ALL SELECT 0, 0, CalculatedValue FROM SourceData WHERE FixHours = 0 AND AddHours = 0

    If it is important, then you need to add rownumber to SourceData, and sort the result of the union by it.

    • In this variant, the result does not include the lines where both FixHours and AddHours are simultaneously equal to zero. Who knows, what if there are such people in the sample? - Yaant
    • @Yaant ok, I agree. added (although then the answer is almost the same as yours except formatting :) - PashaPash ♦

    If there is a desire without any unions, you can join on a table that returns 2 records and then choose what to show by the conditions.

    Those. Something like this option (not tested, but it should work, I can not vouch for the performance of this solution either):

     select case spt.number when 0 then t.FixHours else 0 end as FixHours, case spt.number when 1 then t.AddHours else 0 end as AddHours, t.CalculatedValue from table t join master..spt_values spt on spt.type='p' and spt.number in (0,1) and ((t.FixHour > 0 and spt.number = 0) or (t.AddHours > 0 and spt.number = 1) or (t.FixHour = 0 and t.AddHours = 0 and spt.number = 0)) 
       select fixhours,0,calculatevalue from a where a.fixhours!=0 and a.addhours!=0 union select 0 ,addhours, calculatevalue from a where a.fixhours!=0 and a.addhours!=0

      Solution example

      • A good idea. Need to try. Thank! - Ilya Trifonov
       ;WITH cte (FixHours, AddHours, CalculatedValue) AS ( -- тут должен быть запрос, возвращающий исходную таблицу SELECT 10, 0, 14 UNION ALL SELECT 0.5, 0, 14.5 UNION ALL SELECT 0.5, 1.5, 16.5 UNION ALL SELECT 0, 0.5, 17 UNION ALL SELECT 7, 0.75, 17.75 UNION ALL SELECT 0, 2, 19.75 ) SELECT FixHours, AddHours, CalculatedValue FROM cte WHERE FixHours = 0 OR AddHours = 0 UNION ALL SELECT FixHours, 0, CalculatedValue FROM cte WHERE FixHours <> 0 AND AddHours <> 0 UNION ALL SELECT 0, AddHours, CalculatedValue FROM cte WHERE FixHours <> 0 AND AddHours <> 0 ORDER BY CalculatedValue -- ну или какой там требуется быть порядок