急急急！！！SQL根据逗号列转行，复制其他列

[和永恒有關]

CREATE TABLE #test
(
	ID UNIQUEIDENTIFIER,
	NAME NVARCHAR(50),
	SORTNO int
)

INSERT INTO #test
        ( ID, NAME, SORTNO )
VALUES  ( NEWID(), -- ID - uniqueidentifier
          N'aa,bb,cc,dd', -- NAME - nvarchar(50)
          1  -- SORTNO - int
          )
INSERT INTO #test
        ( ID, NAME, SORTNO )
VALUES  ( NEWID(), -- ID - uniqueidentifier
          N'qqq,www,eee', -- NAME - nvarchar(50)
          3  -- SORTNO - int
          )   

查询结果：
ID NAME SORTNO
82F9E4C3-6733-4ADF-A3A9-D2387ACFC3C0 aa,bb,cc,dd 1
33C29629-AEA1-4A90-A8D1-057C72EB7556 qqq,www,eee 3

想要的结果：
ID NAME SORTNO
82F9E4C3-6733-4ADF-A3A9-D2387ACFC3C0 aa 1
82F9E4C3-6733-4ADF-A3A9-D2387ACFC3C0 bb 1
82F9E4C3-6733-4ADF-A3A9-D2387ACFC3C0 cc 1
82F9E4C3-6733-4ADF-A3A9-D2387ACFC3C0 dd 1
33C29629-AEA1-4A90-A8D1-057C72EB7556 qqq 3
33C29629-AEA1-4A90-A8D1-057C72EB7556 www 3
33C29629-AEA1-4A90-A8D1-057C72EB7556 eee 3


PS: 说简单些就是列转行，但是其他的列要跟着复制过来！！！谢诸位

[和永恒有關]

沙发············

[發糞塗牆]

  
select
    id, 
    --a.name, 
    SUBSTRING(a.name,number,CHARINDEX(',',a.name+',',number)-number) as NAME ,SORTNO
from
    #test a,master..spt_values 
where
    number >=1 and number<=len(a.name)  
    and type='p' 
    and substring(','+a.name,number,1)=','

[發糞塗牆]

CREATE TABLE #test
(
    ID UNIQUEIDENTIFIER,
    NAME NVARCHAR(50),
    SORTNO int
)
 
INSERT INTO #test
        ( ID, NAME, SORTNO )
VALUES  ( NEWID(), -- ID - uniqueidentifier
          N'aa,bb,cc,dd', -- NAME - nvarchar(50)
          1  -- SORTNO - int
          )
INSERT INTO #test
        ( ID, NAME, SORTNO )
VALUES  ( NEWID(), -- ID - uniqueidentifier
          N'qqq,www,eee', -- NAME - nvarchar(50)
          3  -- SORTNO - int
          )   

  
select
    id, 
    --a.name, 
    SUBSTRING(a.name,number,CHARINDEX(',',a.name+',',number)-number) as NAME ,SORTNO
from
    #test a,master..spt_values 
where
    number >=1 and number<=len(a.name)  
    and type='p' 
    and substring(','+a.name,number,1)=','
	/*
	id                                   NAME                                               SORTNO
------------------------------------ -------------------------------------------------- -----------
E8D17CD6-735E-45CB-ABA8-C26B4830C80D aa                                                 1
E8D17CD6-735E-45CB-ABA8-C26B4830C80D bb                                                 1
E8D17CD6-735E-45CB-ABA8-C26B4830C80D cc                                                 1
E8D17CD6-735E-45CB-ABA8-C26B4830C80D dd                                                 1
C94FFF69-5870-47F8-ACC6-DE997F765577 qqq                                                3
C94FFF69-5870-47F8-ACC6-DE997F765577 www                                                3
C94FFF69-5870-47F8-ACC6-DE997F765577 eee                                                3
	*/

[lzw_0736]

SELECT a.id,b.NAME,a.SORTNO
FROM
(SELECT id,SORTNO, NAME=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(NAME)),',','</v><v>')+'</v></root>') FROM #test) a
OUTER APPLY
(SELECT NAME = C.v.value('.','NVARCHAR(MAX)') FROM a.NAME.nodes('/root/v') C(v)) b

[阳泉酒家小当家]

CREATE TABLE #test
(
	ID UNIQUEIDENTIFIER,
	NAME NVARCHAR(50),
	SORTNO int
)

INSERT INTO #test
        ( ID, NAME, SORTNO )
VALUES  ( NEWID(), -- ID - uniqueidentifier
          N'aa,bb,cc,dd', -- NAME - nvarchar(50)
          1  -- SORTNO - int
          )
INSERT INTO #test
        ( ID, NAME, SORTNO )
VALUES  ( NEWID(), -- ID - uniqueidentifier
          N'qqq,www,eee', -- NAME - nvarchar(50)
          3  -- SORTNO - int
          )   


select id,
       --name,
       SUBSTRING(t.name, number ,CHARINDEX(',',t.name+',',number)-number) as name,
       SORTNO
from #test t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.name,s.number,1) = ','
/*
id	name	SORTNO
185CEB33-EE5E-4556-8C5F-3B8CBC3FD5F8	aa	1
185CEB33-EE5E-4556-8C5F-3B8CBC3FD5F8	bb	1
185CEB33-EE5E-4556-8C5F-3B8CBC3FD5F8	cc	1
185CEB33-EE5E-4556-8C5F-3B8CBC3FD5F8	dd	1
DA609361-62DC-4A34-92F3-7B4220CB9503	qqq	3
DA609361-62DC-4A34-92F3-7B4220CB9503	www	3
DA609361-62DC-4A34-92F3-7B4220CB9503	eee	3
*/