Sql同一行逗号隔开转成列

[xiye_jfb]

数据：
a b
X1 1,4,8
X2 2
X3 3,6
X4 7
X5 5
結果
a b
X1 1
X1 4
X1 8
X2 2
X3 3
X3 6
X4 7
X5 5
把同一行逗號隔開的轉成列

[xiye_jfb]

引用 1 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-08 11:50:53
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([a] nvarchar(4),[b] nvarchar(10))
insert [huang]
select 'X1','1,4,8' union all
select 'X2','2' union all
select 'X3','3,6' union all
select 'X4','7' union all
select 'X5','5'
--------------生成数据--------------------------

select
   
    a.[a], 
    SUBSTRING([b],number,CHARINDEX(',',[b]+',',number)-number) as [b] 
from
    [huang] a,master..spt_values 
where
    number >=1 and number<=len([b])  
    and type='p' 
    and substring(','+[b],number,1)=','
----------------结果----------------------------
/* 
a    b
---- ----------
X1   1
X1   4
X1   8
X2   2
X3   3
X3   6
X4   7
X5   5
*/

版主能解析一下這條SQL嗎？=下就結賬了，因為我還看不懂，原諒我無知的請求。

[發糞塗牆]

master..spt_values 这个是系统表，存放了一些数据，大部分人用这个是为了产生一个从1~n的序列，你完全可以自己产生一个，然后。。。。还是自己一步一步研究一下吧。我回写但是不知道怎么解释

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-08 11:50:53
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([a] nvarchar(4),[b] nvarchar(10))
insert [huang]
select 'X1','1,4,8' union all
select 'X2','2' union all
select 'X3','3,6' union all
select 'X4','7' union all
select 'X5','5'
--------------生成数据--------------------------

select
   
    a.[a], 
    SUBSTRING([b],number,CHARINDEX(',',[b]+',',number)-number) as [b] 
from
    [huang] a,master..spt_values 
where
    number >=1 and number<=len([b])  
    and type='p' 
    and substring(','+[b],number,1)=','
----------------结果----------------------------
/* 
a    b
---- ----------
X1   1
X1   4
X1   8
X2   2
X3   3
X3   6
X4   7
X5   5
*/