把以',' 分隔的列转换为行,读取出来

现在表中有如下记录：
ID RoomNum Price
1 102,103 320
2 104 450

我现在需要一条SQL 语句查出要如下结果 ：
ID RoomNum Price
1 102 320
2 103 320
3 104 450

RoomNum是用,分割的. 原问题在此

 

原问题需要用一条SQL来解决, 我实在想不出, 所有使用存储过程.

如果有能用一条SQL解决的朋友, 请不吝赐教.

 

下面说一下解决的思路很简单.

• 遍历整个数据表, 这里面需要使用的是游标.
• 找到RoomNum列中含有 ',' 的行, 这里需要进行判定, RoomNum中是否含有',', 用charindex函数来判断.
• 将含有','的RoomNum使用stuff()进行循环拆分, 把字符一个一个的取出来.
• 将取出来的字符再添加到临时表中.
• 如果RoomNUm中不含有',', 则可以直接添加到临时表中.

需要说一点的是, 如果不喜欢用临时表的, 可以使用table变量.

示例: declare @table(int int, RoomNum,varchar(200),Price varchar(200))

具体的操作和操作数据表一样. 可以insert @table(列名) values(值)

 

存储过程如下: 编辑器MS不支持SQL的语法高亮, 只能原样奉上了.

create procedure sp_SplitColumnsIntoMultiRows
as
begin
SET NOCOUNT ON;
create table #TempTable(id int, RoomNum varchar(200),Price varchar(200))

declare @split char(1)
set @split = ','
   
declare @id int
declare @roomnum varchar(255)
declare @price varchar(255)
declare t_cursor cursor for select id,RoomNum,Price from test 
-- 定义参数及游标,以遍历整个数据表(此处表名为test,改为实际的表)
open t_cursor
fetch next from t_cursor into @id,@roomnum,@price
-- 遍历整个数据表, 将每一条记录的值记录到变量中.
while(@@fetch_status=0)
    begin
        --如果roomnum中包含了分隔符,则进行拆分.
        while(charindex(@split,@roomnum)<>0) 
        begin 
            insert #TempTable(id,RoomNum,Price) values(@id,substring(@roomnum,1,charindex(@split,@roomnum)-1),@price)
            -- 拆分,将已经记录到表中的字符删除
            set @roomnum = stuff(@roomnum,1,charindex(@split,@roomnum),'') 
        end
        --不包含,则直接记录到表中 
        insert #TempTable(id,RoomNum,Price) values (@id,@roomnum,@price)
        fetch next from t_cursor into @id,@roomnum,@price   
    end
     
end
--关闭游标,释放游标
close t_cursor
deallocate t_cursor
select * from #TempTable
 
go