Sql 列转行

set statistics io on
set statistics time ON

--主从表可用
SELECT vcaa, vcbb, ISNULL(( SELECT vcCN + ','
FROM TableB b
WHERE a.vcaa= b.vcaa
FOR
XML PATH('') ), '') AS vcNameList
FROM TableA a

--单表
SET @vcResult =(SELECT vcID+ ',' 
        FROM table_A
        WHERE vcSerialNo = @vcOrderID
        FOR
        XML PATH(''))

SELECT vcList = (stuff((select ',' + vcId from tb_Table where vcAAA = 123 AND vcBBB = 'abc' for xml path('')),1,1,''))

--varchar型
DECLARE 
     @nTempFinAmount VARCHAR(2000)= '''a;b;c;d;e;f''',
                @str1 VARCHAR(8000) 
                IF object_id(N'tempdb..#temp1',N'U') is not null
                BEGIN
                    DROP TABLE #temp1
                END
                CREATE  TABLE #temp1  (
                    id INT,
                    Amount varchar(50)
                )

                SET @str1 =  'select  Ratio='+replace(@nTempFinAmount,';',''+''' union all select ''')+'' 
                SET @str1=' select id=identity(int,1,1),Ratio  into #temp from ('+@str1+') a ;
                insert into #temp1 select * from #temp;'
                EXEC(@str1)    
                SELECT  * from #temp1



--int型
DECLARE 
                        @str1 VARCHAR(8000) 
                        IF object_id(N'tempdb..#temp1',N'U') is not null
                        BEGIN
                            DROP TABLE #temp1
                        END
                        CREATE  TABLE #temp1  (
                            id INT,
                            Amount DECIMAL(18,2)
                        )
                        SET @str1 =  'select  Ratio='+replace(@nTempFinAmount,',',''+' union all select ')+'' 

                        SET @str1=' select id=identity(int,1,1),Ratio  into #temp from ('+@str1+') a ;
                        insert into #temp1 select * from #temp;'
                        EXEC(@str1)