CODEWITHSUNDEEP
sqlsql-server
jackstraw22
jackstraw22

Reputation: 641

Need two columns in one row in SQL

Given the values below I need records with the same ID to be on the same line ONLY if either the Paid or Interest values are 0 and the other is not. In this case ID= 1 would be on one line, but the others would remain the same.

declare @t table(id int, paid varchar(20), interest varchar (20))
insert into @t values(1, '0.00', '3.51'),
                 (1, '1000', '0.00'),
                 (3, '2.50', '0.00'),
                 (4, '50.00', '2.20'),
                 (4, '75.00', '0.10')
select * from @t

I need a result like this:

   ID     Paid      Interest
   1      1000         3.51
   3      2.50         0.00
   4      50.00        2.20
   4      75.00        0.00

I tried creating something using a windows function but couldn't come close. Anyone have any ideas?

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Hmmm . . . this approach splits the columns apart and then re-combines them.

Try this:

select coalesce(ti.id, tp.id), tp.paid, ti.interest
from (select t.*,
             row_number() over (partition by paid) as seqnum
      from t
      where paid <> 0
     ) tp full join
     (select t.*,
             row_number() over (partition by interest) as seqnum
      from t
      where interest <> 0
     ) ti
     on tp.id = ti.id and tp.seqnum = ti.seqnum;

Upvotes: 1

Related Questions