SQLTeam.com | Weblogs | Forums

Split into multiple rows


#1

ID Category Amount
1 A 120
2 B 100

Result
ID LineID Category Amount
1 1 A 60
1 2 A 60
2 1 B 100

Want to split the amount by 50% if category is A thus creating 2 records.
Union solution doesnot look optimal.

select col1, 1,col3, amount

from t
where col3='A'
union
select col1,1, col3, amount0.5 from
T
where col3='B'
union
select col1,2, col3, amount
0.5 from
T
where col3='B'


#2
declare @tv_Source as table
( id int not null
 ,Category char(1) not null
 ,Amount decimal(8,2) not null
)

insert into @tv_Source(id,category,amount)
values
(1,	'A', 120)
,(2,	'B', 100)

    select S.id
   , case when S.category ='A' then Numb.rn else 1 end as line
   , S.category
   , case when S.category ='A' then S.amount*0.5 else S.amount*1.0 end as amount
from @tv_Source as s
left join 
(select 1 as rn , 'A' as category 
 union all select 2,'A')numb
 on s.category ='A'

output of it:

id	line	category	amount
1	1	A	60.000
1	2	A	60.000
2	1	B	100.000