Share via

Handle with Delimeter ^ (Continuation from Previous Question-2)

jn93 671 Reputation points
2022-11-04T02:14:29.533+00:00

Hi All,

This is continuation from previous two question. I need to do one more enhancement from the previous solution given by @Yitzhak Khabinsky . Lets say I have one more column Nationality without delimiter "^". As per in code below, I cannot follow the SQL code like for filename field(same info for entire table). How can I populate the result like below?
If you notice the UKEY is repeated for each action of ^. Also how can I insert counting for UKEY like below. The reason is to insert primary key in my table which is combination of UKEY and UKEY_COUNT. I have copy paste the previous T-SQL code and also new sample DDL with changes. Kindly please let me know the solution if there is way to enhance below T_SQL code. 

-- DDL and sample data population, start   
  
DECLARE @tbl TABLE (AUTONUM VARCHAR(50), UKEY VARCHAR(100), INSCODE VARCHAR(50), ACTION_TIMESTAMP VARCHAR(100), LOAD_DATETIME DATETIME, FILENAME VARCHAR(100), NATIONALITY VARCHAR(50));   
INSERT INTO @tbl (AUTONUM, UKEY, INSCODE, ACTION_TIMESTAMP, LOAD_DATETIME, FILENAME, NATIONALITY)   
VALUES ('2287250^2287307^2287308^2287309','91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265','91^91^91^91','20220817163329^20220817165355^20220817165358^20220817165359','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','MAL')  
, ('2287288^2287303^2287304','91TMIQUO6124293^91TMIQUO6124293^91TMIQUO6124293','91^91^91','20220817164753^20220817165318^20220817165322','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','DEN');   
  
-- DDL and sample data population, end  
  
DECLARE @LOAD_DATETIME DATETIME , @FILENAME VARCHAR(100);   
SELECT TOP(1) @LOAD_DATETIME = LOAD_DATETIME, @FILENAME = FILENAME FROM @tbl;   
DECLARE @separator CHAR(1) = '^';   
;WITH rs AS   
( SELECT *   
FROM @tbl AS t   
			CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
				REPLACE(AUTONUM, @separator, ']]></r><r><![CDATA[') +  
				']]></r></root>' AS XML)) AS t1(a)   
			CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
				REPLACE(UKEY, @separator, ']]></r><r><![CDATA[') +  
				']]></r></root>' AS XML)) AS t2(b)   
			CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +  
				REPLACE(INSCODE, @separator, ']]></r><r><![CDATA[') +  
				']]></r></root>' AS XML)) AS t3(c)   
			CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
				+ REPLACE(ACTION_TIMESTAMP, @separator, ']]></r><r><![CDATA[') +  
				']]></r></root>' AS XML)) AS t4(d)   
	), rs_AUTONUM AS   
	(   
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID   
			, t.c.value('(./text())[1]', 'VARCHAR(50)') AS AUTONUM   
		FROM rs CROSS APPLY a.nodes('/root/r') AS t(c)   
	)   
	, rs_UKEY AS   
	(   
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID   
			, t.c.value('(./text())[1]', 'VARCHAR(100)') AS UKEY   
		FROM rs CROSS APPLY b.nodes('/root/r') AS t(c)   
	), rs_INSCODE AS   
	(   
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID   
			, t.c.value('(./text())[1]', 'VARCHAR(50)') AS INSCODE   
		FROM rs CROSS APPLY c.nodes('/root/r') AS t(c)   
	), rs_ACTION_TIMESTAMP AS   
	(   
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID   
			, t.c.value('(./text())[1]', 'VARCHAR(100)') AS ACTION_TIMESTAMP   
		FROM rs CROSS APPLY d.nodes('/root/r') AS t(c)   
	)   
	SELECT x1.*, x2.UKEY, x3.INSCODE, x4.ACTION_TIMESTAMP , @LOAD_DATETIME AS LOAD_DATETIME, @FILENAME AS FILENAME   
	FROM rs_AUTONUM AS x1 INNER JOIN rs_UKEY AS x2 ON x1.ID = x2.ID   
		INNER JOIN rs_INSCODE AS x3 ON x2.ID = x3.ID   
		INNER JOIN rs_ACTION_TIMESTAMP AS x4 ON x3.ID = x4.ID   
	ORDER BY x1.ID;

257017-image.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. LiHongMSFT-4306 25,651 Reputation points
    2022-11-04T06:03:50.287+00:00

    Hi @jn93
    How about this: 

    -- DDL and sample data population, start    
DECLARE @tbl TABLE (AUTONUM VARCHAR(50), UKEY VARCHAR(100), INSCODE VARCHAR(50), ACTION_TIMESTAMP VARCHAR(100), LOAD_DATETIME DATETIME, FILENAME VARCHAR(100), NATIONALITY VARCHAR(50));   
INSERT INTO @tbl (AUTONUM, UKEY, INSCODE, ACTION_TIMESTAMP, LOAD_DATETIME, FILENAME, NATIONALITY)   
VALUES   
 ('2287250^2287307^2287308^2287309','91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265','91^91^91^91','20220817163329^20220817165355^20220817165358^20220817165359','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','MAL')  
,('2287288^2287303^2287304','91TMIQUO6124293^91TMIQUO6124293^91TMIQUO6124293','91^91^91','20220817164753^20220817165318^20220817165322','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','DEN');     
 -- DDL and sample data population, end  
      
 DECLARE @LOAD_DATETIME DATETIME , @FILENAME VARCHAR(100);   
 SELECT TOP(1) @LOAD_DATETIME = LOAD_DATETIME, @FILENAME = FILENAME FROM @tbl;   
 DECLARE @separator CHAR(1) = '^';   
 ;WITH rs AS   
 ( SELECT *   
 FROM @tbl AS t   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
                 REPLACE(AUTONUM, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t1(a)   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
                 REPLACE(UKEY, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t2(b)   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +  
                 REPLACE(INSCODE, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t3(c)   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
                 + REPLACE(ACTION_TIMESTAMP, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t4(d)   
     ), rs_AUTONUM AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY NATIONALITY DESC) AS ID ,NATIONALITY  
             , t.c.value('(./text())[1]', 'VARCHAR(50)') AS AUTONUM   
         FROM rs CROSS APPLY a.nodes('/root/r') AS t(c)   
     )   
     , rs_UKEY AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY NATIONALITY DESC) AS ID  
             , t.c.value('(./text())[1]', 'VARCHAR(100)') AS UKEY   
         FROM rs CROSS APPLY b.nodes('/root/r') AS t(c)   
     ), rs_INSCODE AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY NATIONALITY DESC) AS ID  
             , t.c.value('(./text())[1]', 'VARCHAR(50)') AS INSCODE   
         FROM rs CROSS APPLY c.nodes('/root/r') AS t(c)   
     ), rs_ACTION_TIMESTAMP AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY NATIONALITY DESC) AS ID  
             , t.c.value('(./text())[1]', 'VARCHAR(100)') AS ACTION_TIMESTAMP   
         FROM rs CROSS APPLY d.nodes('/root/r') AS t(c)   
     )   
     SELECT x1.ID,x1.AUTONUM, x2.UKEY, x3.INSCODE, x4.ACTION_TIMESTAMP , @LOAD_DATETIME AS LOAD_DATETIME, @FILENAME AS FILENAME, x1.NATIONALITY  
	       ,ROW_NUMBER()OVER(PARTITION BY x1.NATIONALITY ORDER BY x1.AUTONUM) AS UKEY_COUNT  
     FROM rs_AUTONUM AS x1 INNER JOIN rs_UKEY AS x2 ON x1.ID = x2.ID   
         INNER JOIN rs_INSCODE AS x3 ON x2.ID = x3.ID   
         INNER JOIN rs_ACTION_TIMESTAMP AS x4 ON x3.ID = x4.ID   
     ORDER BY x1.ID;

    Output:
    257111-image.png

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHongMSFT-4306 25,651 Reputation points
    2022-11-04T02:51:24.763+00:00

    Hi @jn93
    Try this query: 

    -- DDL and sample data population, start    
DECLARE @tbl TABLE (AUTONUM VARCHAR(50), UKEY VARCHAR(100), INSCODE VARCHAR(50), ACTION_TIMESTAMP VARCHAR(100), LOAD_DATETIME DATETIME, FILENAME VARCHAR(100), NATIONALITY VARCHAR(50));   
INSERT INTO @tbl (AUTONUM, UKEY, INSCODE, ACTION_TIMESTAMP, LOAD_DATETIME, FILENAME, NATIONALITY)   
VALUES   
 ('2287250^2287307^2287308^2287309','91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265','91^91^91^91','20220817163329^20220817165355^20220817165358^20220817165359','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','MAL')  
,('2287288^2287303^2287304','91TMIQUO6124293^91TMIQUO6124293^91TMIQUO6124293','91^91^91','20220817164753^20220817165318^20220817165322','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','DEN');     
 -- DDL and sample data population, end  
      
DECLARE @separator CHAR(1) = '^';  
  
;WITH CTE AS   
(  
 SELECT AUTONUM = '["' + REPLACE(AUTONUM, @separator, '","') + '"]'  
       ,UKEY = '["' + REPLACE(UKEY, @separator, '","') + '"]'  
	   ,INSCODE = '["' + REPLACE(INSCODE, @separator, '","') + '"]'  
	   ,ACTION_TIMESTAMP = '["' + REPLACE(ACTION_TIMESTAMP, @separator, '","') + '"]'  
	   ,LOAD_DATETIME  
	   ,FILENAME  
	   ,NATIONALITY  
 FROM @tbl  
)  
SELECT ROW_NUMBER()OVER(ORDER BY NATIONALITY DESC,AUTONUM) AS ID  
      ,AUTONUM.[value] AS AUTONUM  
	  ,UKEY.[value] AS UKEY  
	  ,INSCODE.[value] AS INSCODE  
	  ,ACTION_TIMESTAMP.[value] AS ACTION_TIMESTAMP  
	  ,LOAD_DATETIME  
	  ,FILENAME  
	  ,NATIONALITY  
	  ,ROW_NUMBER()OVER(PARTITION BY NATIONALITY ORDER BY AUTONUM) AS UKEY_COUNT  
FROM CTE  
     CROSS APPLY OPENJSON (AUTONUM, N'$') AS AUTONUM  
     CROSS APPLY OPENJSON (UKEY, N'$') AS UKEY  
	 CROSS APPLY OPENJSON (INSCODE, N'$') AS INSCODE  
	 CROSS APPLY OPENJSON (ACTION_TIMESTAMP, N'$') AS ACTION_TIMESTAMP  
WHERE AUTONUM.[key] = UKEY.[key] AND AUTONUM.[key] = INSCODE.[key] AND AUTONUM.[key] = ACTION_TIMESTAMP.[key]  
ORDER BY ID

    Output:
    257064-image.png

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. LiHongMSFT-4306 25,651 Reputation points
    2022-11-04T03:01:24.36+00:00

    Hi @jn93
    How about this modification based on your initial query: 

    -- DDL and sample data population, start    
DECLARE @tbl TABLE (AUTONUM VARCHAR(50), UKEY VARCHAR(100), INSCODE VARCHAR(50), ACTION_TIMESTAMP VARCHAR(100), LOAD_DATETIME DATETIME, FILENAME VARCHAR(100), NATIONALITY VARCHAR(50));   
INSERT INTO @tbl (AUTONUM, UKEY, INSCODE, ACTION_TIMESTAMP, LOAD_DATETIME, FILENAME, NATIONALITY)   
VALUES   
 ('2287250^2287307^2287308^2287309','91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265','91^91^91^91','20220817163329^20220817165355^20220817165358^20220817165359','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','MAL')  
,('2287288^2287303^2287304','91TMIQUO6124293^91TMIQUO6124293^91TMIQUO6124293','91^91^91','20220817164753^20220817165318^20220817165322','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt','DEN');     
 -- DDL and sample data population, end  
      
 DECLARE @LOAD_DATETIME DATETIME , @FILENAME VARCHAR(100);   
 SELECT TOP(1) @LOAD_DATETIME = LOAD_DATETIME, @FILENAME = FILENAME FROM @tbl;   
 DECLARE @separator CHAR(1) = '^';   
 ;WITH rs AS   
 ( SELECT *   
 FROM @tbl AS t   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
                 REPLACE(AUTONUM, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t1(a)   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
                 REPLACE(UKEY, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t2(b)   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +  
                 REPLACE(INSCODE, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t3(c)   
             CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
                 + REPLACE(ACTION_TIMESTAMP, @separator, ']]></r><r><![CDATA[') +  
                 ']]></r></root>' AS XML)) AS t4(d)   
     ), rs_AUTONUM AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID ,NATIONALITY  
             , t.c.value('(./text())[1]', 'VARCHAR(50)') AS AUTONUM   
         FROM rs CROSS APPLY a.nodes('/root/r') AS t(c)   
     )   
     , rs_UKEY AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
             , t.c.value('(./text())[1]', 'VARCHAR(100)') AS UKEY   
         FROM rs CROSS APPLY b.nodes('/root/r') AS t(c)   
     ), rs_INSCODE AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
             , t.c.value('(./text())[1]', 'VARCHAR(50)') AS INSCODE   
         FROM rs CROSS APPLY c.nodes('/root/r') AS t(c)   
     ), rs_ACTION_TIMESTAMP AS   
     (   
         SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
             , t.c.value('(./text())[1]', 'VARCHAR(100)') AS ACTION_TIMESTAMP   
         FROM rs CROSS APPLY d.nodes('/root/r') AS t(c)   
     )   
     SELECT x1.ID,x1.AUTONUM, x2.UKEY, x3.INSCODE, x4.ACTION_TIMESTAMP , @LOAD_DATETIME AS LOAD_DATETIME, @FILENAME AS FILENAME, x1.NATIONALITY  
	       ,ROW_NUMBER()OVER(PARTITION BY x1.NATIONALITY ORDER BY x1.AUTONUM) AS UKEY_COUNT  
     FROM rs_AUTONUM AS x1 INNER JOIN rs_UKEY AS x2 ON x1.ID = x2.ID   
         INNER JOIN rs_INSCODE AS x3 ON x2.ID = x3.ID   
         INNER JOIN rs_ACTION_TIMESTAMP AS x4 ON x3.ID = x4.ID   
     ORDER BY x1.ID;