使用SQL Server动态PIVOT查询

Question

我有以下表格Account

Accountid            Calcul 
1                    27+23+12
4                    5+9+12
7                    7+12+20

我希望得到以下表AccountTemp

Accountid                AccountCode
    1                    27
    1                    23
    1                    12
    4                    5
    4                    9
    4                    12 
    7                    7
    7                    12
    7                    20

Answer 1

declare @account table(Accountid int, Calcul varchar(20))
--AccountTemp
insert @account values(1, '27+23+12')
insert @account values(4,'5+9+12')
insert @account values(7,'7+12+20')

create table #accounttemp(Accountid int, AccountCode int)
insert #accounttemp(Accountid, AccountCode)
SELECT Accountid, t.c.value('.', 'INT') AccountCode
FROM (
    SELECT Accountid, CAST('<t>' + 
        REPLACE(Calcul, '+', '</t><t>') + '</t>' AS XML) x
        FROM @account
) a
CROSS APPLY x.nodes('/t') t(c)
select * from #accounttemp
drop table #accounttemp

结果:

Accountid   AccountCode
1           27
1           23
1           12
4           5
4           9
4           12
7           7
7           12
7           20

Answer 2

你可以这样做:

创建如下的split函数:

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

然后执行如下查询:

SELECT
    tbl.Accountid,
    split.AccountCode
FROM
    @tbl AS tbl
    CROSS APPLY
    (
        SELECT
            CAST(calc.Data AS INT) AS AccountCode
        FROM
            dbo.split(tbl.Calcul,'+') as calc
    ) as split

输出:

Accountid    AccountCode
1            27
1            23
1            12
4            5
4            9
4            12
7            7
7            12
7            20

参考:

• 使用T-SQL
将一个字符串拆分为一个表
• 交叉应用说明