如何将动态变量值分为单个列

Question

我有以下代码返回一个带有以下值的变量@Colsrate：

'[5.00],[5.00],[1.00],[35.00],[1.00],[1.00],[1.00],[1.00],[1.00],[1.00]' 

我需要将此单个值转换为单个列。我怎样才能做到这一点？谢谢。

SELECT 
    CAST((CAST(Rate AS DECIMAL(18,2))) AS NVARCHAR(255))AS Rate 
    ,LEFT((CAST(StartDate AS NVARCHAR(255))), 11) AS StartDate
    INTO #TempTab
    FROM RepoCost.vwCurrencyRate
WHERE 
    CurrencyNameFrom = @CurrencyNameFrom
AND 
    CurrencyNameTo=@CurrencyNameTo
AND 
    DimdateId= @DimdateId
SET @colsrate =  STUFF((SELECT ','+QUOTENAME(Rate)
                    FROM #TempTab
      FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

Answer 1

尝试以下

IF OBJECT_ID('Tempdb..#Temp') IS NOT NULL
Drop table #Temp
Declare @DyValue TABLE (Value nvarchar(max))
INSERT INTO @DyValue(Value)
SELECT '[5.00],[5.00],[1.00],[35.00],[1.00],[1.00],[1.00],[1.00],[1.00],[1.00]' 
Declare @DynamicCol nvarchar(max),@Sql nvarchar(max)
SELECT * INTO #Temp FROM
(
SELECT Row_number()Over(Order by (SELECT NULL)) AS ID,Split.a.value('.', 'VARCHAR(1000)') AS Value
            FROM  (
                SELECT  CAST('<S>' + REPLACE(Value, ',', '</S><S>') + '</S>' AS XML) AS Value
                FROM @DyValue
                ) AS A
            CROSS APPLY Value.nodes('/S') AS Split(a)
            )Dt

    SELECT @DynamicCol=STUFF((SELECT ', ' + ''''+CAST( Value AS Varchar(10))+'''' From #Temp
            FOR XML PATH ('')),1,1,'')  
            SELECT @DynamicCol AS ExpectedValue
    SET @Sql ='SELECT '+@DynamicCol 
    EXECUTE @Sql
    PRINT @Sql

它像下面的印刷

一样

SELECT  '[5.00]', '[5.00]', '[1.00]', '[35.00]', '[1.00]', '[1.00]', '[1.00]', '[1.00]', '[1.00]', '[1.00]'

Answer 2

此代码不是我的。这是John Cappelletti的解决方案(这是原始链接(

，但完美地满足您的需求

Declare @YourTable table (SomeCol varchar(max))
Insert Into @YourTable values
('[5.00],[5.00],[1.00],[35.00],[1.00],[1.00],[1.00],[1.00],[1.00],[1.00]')

Select B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(A.SomeCol,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B