将数据拆分成多行

Question

    State      City
    DL,UP      DELHI: Karol Bag,Ashok Nagr UttarPradesh: Noida,Lucknow

OutPut

    State      City
    DL         KarolBag
    DL         Ashok Nagr
    UP         Noida
    UP         Lucknow

我已经创建了一个拆分值的函数，但是当交叉应用这个函数时，它给出了不合适的结果，比如DL-Lucknow。我想要确切的结果。

Answer 1

试试这个：

我不认为这是一种有效的方法。但是，如果您的数据存储在相同的模式中，它会给出解决方案。

DECLARE @City VARCHAR(50) = 'DL,UP', @Str VARCHAR(150)='DELHI: Karol Bag,Ashok Nagr UttarPradesh: Noida,Lucknow'

DECLARE @STr1 VARCHAR(150),@STr2 VARCHAR(150)

SELECT @Str1=REVERSE(RIGHT(REVERSE(LEFT(@Str,LEN(@Str)-CHARINDEX(':',REVERSE(@Str)))),LEN(REVERSE(LEFT(@Str,LEN(@Str)-CHARINDEX(':',REVERSE(@Str)))))-CHARINDEX(' ', REVERSE(LEFT(@Str,LEN(@Str)-CHARINDEX(':',REVERSE(@Str)))))))
    ,@STr2=RIGHT(@Str,LEN((RIGHT(REVERSE(LEFT(@Str,LEN(@Str)-CHARINDEX(':',REVERSE(@Str)))),LEN(REVERSE(LEFT(@Str,LEN(@Str)-CHARINDEX(':',REVERSE(@Str)))))-CHARINDEX(' ', REVERSE(LEFT(@Str,LEN(@Str)-CHARINDEX(':',REVERSE(@Str)))))))))

SELECT @STr1=RIGHT(@STr1,LEN(@STr1)-(CHARINDEX(':', @STr1)+1)),@STr2=RIGHT(@STr2,LEN(@STr2)-(CHARINDEX(':', @STr2)+1))

SELECT A.value [State],D.value City
FROM dbo.fn_Split(@City,',') A
INNER JOIN(
            SELECT 0 ID,* FROM dbo.fn_Split(@STr1,',')
            UNION
            SELECT 1,* FROM dbo.fn_Split(@STr2,',')
          )D ON D.ID=A.idx

OutPut:

State   City
DL      Karol Bag
DL      Ashok Nagr
UP      Noida
UP      Lucknow

Answer 2

我不会说以这种方式存储数据是个好主意，但如果是这样，您可以尝试这种解决方案(基于您的示例)

--------------------------------------------------------------------------------
--create sample data set
DECLARE @tbl AS TABLE([State] VARCHAR(100), City VARCHAR(1000));
INSERT INTO @tbl(State, City)
VALUES('DL,UP,Some', 'DELHI: Karol Bag,Ashok Nagr UttarPradesh: Noida,Lucknow SomeState: Same City1, Some City2');
----------------------------------------------------------
--Create sequence (can be replaced by recursive cte)
DECLARE @Tally TABLE(N INT);
DECLARE @i AS INT=1;
WHILE @i !=1000 BEGIN
    INSERT INTO @Tally(N)VALUES(@i);
    SET @i+=1;
END;
----------------------------------------------------------
--Query (2 cte to split each fied and assign ID)
;WITH StatesSplit AS 
    (SELECT StateID=ROW_NUMBER() OVER (ORDER BY N), 
            StateShort=REPLACE(REPLACE(P1, LEAD(P1, 1, '') OVER (ORDER BY N), ''), ',', '')
     FROM @tbl AS E
        INNER JOIN @Tally AS T ON SUBSTRING(','+E.State, T.N, 1)=','
        CROSS APPLY(SELECT STUFF(','+E.State, 1, n, '') AS P1) AS Stage1 
     ), 
CitiesSplit AS 
    (SELECT StateLong = REPLACE(States, ':', ''), 
            ComboCities = REPLACE(REPLACE(P2, LEAD(P2, 1, '') OVER (ORDER BY N), ''), States, ''), 
            StateID=ROW_NUMBER() OVER (ORDER BY N)
    FROM @tbl AS E
        INNER JOIN @Tally AS T ON SUBSTRING(E.City, T.N, 1)=':'
        CROSS APPLY(SELECT REVERSE(STUFF(E.City, n+1, 999, '')) AS P1) AS Stage1
        CROSS APPLY(SELECT REVERSE(STUFF(P1+' ', CHARINDEX(' ', P1+' '), 999, '')) AS States) AS Stage2
        CROSS APPLY(SELECT STUFF(' '+City, 1, PATINDEX('%'+States+'%', City), '') AS P2) AS Stage3 
    )
--Final Output
SELECT S.StateShort, 
       C.StateLong, 
       Cities=LTRIM(REPLACE(REPLACE(P1, LEAD(P1, 1, '') OVER (PARTITION BY S.StateShort ORDER BY N), ''), ',', ''))
FROM StatesSplit AS S
     JOIN CitiesSplit AS C ON S.StateID=C.StateID
     INNER JOIN @Tally AS T ON SUBSTRING(','+C.ComboCities, T.N, 1)=','
     CROSS APPLY(SELECT STUFF(','+C.ComboCities, 1, n, '') AS P1) AS Stage1
ORDER BY 1

测试如下：

https://data.stackexchange.com/stackoverflow/query/822004/ccccombo

​

​