SQL sever 按条件拆分每行数据怎么实现

比如我的是数据是左边的图，想做成右边的图

如果类别是A，那么不用改变，直接放在一行里；
如果类别是B，那么就是从它‘代码’里抽出3个，每种抽法放一行，比如4选3，就要拆成4行；
如果类别是C，那么从中括号里选择一个配上括号外的数字组成三个数字，每种情况占一行。
每一种拆分都要新建成单独一行。要用case when么？then之后是不是不能插入特别长的，因为之后还要把“1+2+6”拆分为三列，所以比较麻烦
求助！多谢多谢了！回答细致可以提高悬赏分

DECLARE @Room INT,
@Category NCHAr(50),
@Price DECIMAL(10,2),
@StartDate DATE,
@EndDate DATE,
@Index INT,
@TotalDayDiff INT,
@CurrentMonthStart DATE,
@CurrentMonthEnd DATE

DECLARE @Test TABLE
(
ID INT IDENTITY(1,1),
Room INT,
Category NCHAr(50),
Price DECIMAL(10,2),
StartDate DATE,
EndDate DATE
)
INSERT @Test SELECt 1905, N'房租', 3000, '2015-02-06','2015-04-05'
UNIOn SELECT 2000, N'你欠的', 5555, '2014-04-20', '2015-02-07'
SELECT * FROM @Test

DECLARE @Test2 TABLE
(
CurrentMonth CHAr(7),
Room INT,
Category NCHAr(50),
Price VARCHAr(50)
)

SET @Index = 1
WHILE @Index <= (SELECt MAX(ID) FROM @Test)
BEGIN
SELECt @Room = Room, @Category = Category, @Price = Price, @StartDate = StartDate, @EndDate = EndDate
FROM @Test
WHERe ID = @Index

SELECt @TotalDayDiff = DATEDIFF(DAY, @StartDate, @EndDate) + 1
WHILE @StartDate <= @EndDate
BEGIN
SET @CurrentMonthStart = @StartDate
IF YEAR(@StartDate) = YEAR(@EndDate) AND MONTH(@StartDate) = MONTH(@EndDate)
BEGIN
SET @CurrentMonthEnd = @EndDate
END
ELSE
BEGIN
SET @CurrentMonthEnd = DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m,0,@StartDate) + 1, 0))
END

INSERT @Test2 VALUES (
CAST(YEAR(@CurrentMonthStart) AS VARCHAR) + ' ' + CAST(MONTH(@CurrentMonthStart) AS VARCHAR),
@Room,
@Category,
CAST(@Price AS VARCHAR) + '*' + CAST(DATEDIFF(DD, @CurrentMonthStart, @CurrentMonthEnd) + 1 AS VARCHAR) + '/' + CAST(@TotalDayDiff AS VARCHAR))
SET @StartDate = DATEADD(MM, 1, DATEADD(mm, DATEDIFF(mm,0,@StartDate), 0))
END

SET @Index = @Index + 1
END
SELECT * FROM @Test2

declare @room int, @category nchar(50), @price decimal(10,2), @startdate date, @enddate date, @index int, @totaldaydiff int, @currentmonthstart date, @currentmonthend date declare @test table ( id int identity(1,1), room int, category nchar(50), price decimal(10,2), startdate date, enddate date ) insert @test select 1905, n'房租', 3000, '2015-02-06','2015-04-05' union select 2000, n'你欠的', 5555, '2014-04-20', '2015-02-07' select * from @test declare @test2 table ( currentmonth char(7), room int, category nchar(50), price varchar(50) ) set @index = 1 while @index begin select @room = room, @category = category, @price = price, @startdate = startdate, @enddate = enddate from @test where id = @index select @totaldaydiff = datediff(day, @startdate, @enddate) + 1 while @startdate begi... union select 2000, datediff(m, @currentmonthstart, price decimal(10, @currentmonthend) + 1 as varchar) + '', startdate date, 0)) end insert @test2 values ( cast(year(@currentmonthstart) as varchar) + ', category nchar(50),@startdate) + 1, @room;2014-04-20', @startdate date;, category nchar(50);/, @enddate date, @enddate = enddate from @test where id = @index select @totaldaydiff = datediff(day, datediff(mm, @category nchar(50), 1,@startdate),1),',0, 3000, dateadd(mm; '你欠的',0, @startdate = startdate, enddate date ) insert @test select 1905;;,2), ', 5555, n', dateadd(mm, room int; select * from @test declare @test2 table ( currentmonth char(7); + cast(@totaldaydiff as varchar)) set @startdate = dateadd(mm, @category, @enddate) + 1 while @startdate <, n', @price decimal(10; + cast(datediff(dd, @price = price;2015-02-07', @category = category;= (select max(id) from @test) begin select @room = room;2015-02-06';*'= @enddate begin set @currentmonthstart = @startdate if year(@startdate) = year(@enddate) and month(@startdate) = month(@enddate) begin set @currentmonthend = @enddate end else begin set @currentmonthend = dateadd(ms, @index int, cast(@price as varchar) + ', ', @totaldaydiff int;房租',2); + cast(month(@currentmonthstart) as varchar), -3, @currentmonthstart datedeclare @room int;2015-04-05', room int, price varchar(50) ) set @index = 1 while @index <, @startdate, @currentmonthend date declare @test table ( id int identity(1, '