Please help with the SQL query without using XML

Question

Hi Team,

Please find the below data:

Column1
1|Johnson Day|Sad|2022-04-05|Authentication|Meta|NULL|NULL|Cancelled
1|Rony Day |Sad|2022-02-25|Authentication|Meta|NULL|NULL|WIP
1|Sam Day |Sad|2022-01-15|Authentication|Meta|NULL|NULL|Stopped
1|Irina Day |Sad|2022-04-05|Authentication|Meta|NULL|NULL|Cancelled
1|Nancy Day |Sad|2022-03-15|Authentication|Meta|NULL|NULL|Cancelled..........

I want all the data to be pipe separated and in different columns as below. Please help me to write that. I have done it using XML but as the data volume is huge need some other ways.

Output should be

Col1 Col2 Col3
1 Johnson Day ..........so on

Here the number of columns will be more than 9. Please tell me the format to write that.

Answer 1

Hi @Subhomoy Chakraborty
Try this:

CREATE TABLE #TEST (COLUMN1 VARCHAR(100))  
INSERT INTO #TEST VALUES  
('1|Johnson Day|Sad|2022-04-05|Authentication|Meta|NULL|NULL|Cancelled'),  
('1|Rony Day |Sad|2022-02-25|Authentication|Meta|NULL|NULL|WIP'),  
('1|Sam Day |Sad|2022-01-15|Authentication|Meta|NULL|NULL|Stopped'),  
('1|Irina Day |Sad|2022-04-05|Authentication|Meta|NULL|NULL|Cancelled'),  
('1|Nancy Day |Sad|2022-03-15|Authentication|Meta|NULL|NULL|Cancelled')  
  
DECLARE @separator CHAR(1) = '|';  
;WITH CTE AS   
(  
 SELECT ROW_NUMBER()OVER(ORDER BY COLUMN1) ID,  '["'+ REPLACE(COLUMN1,@separator, '","') + '"] ' jsCol  
 FROM #TEST  
)  
SELECT DISTINCT  ID,  
       JSON_VALUE(jsCol, '$[0]') AS COLUMN1   
      ,JSON_VALUE(jsCol, '$[1]') AS COLUMN2   
      ,JSON_VALUE(jsCol, '$[2]') AS COLUMN3   
      ,JSON_VALUE(jsCol, '$[3]') AS COLUMN4  
   ,JSON_VALUE(jsCol, '$[4]') AS COLUMN5   
   ,JSON_VALUE(jsCol, '$[5]') AS COLUMN6   
   ,JSON_VALUE(jsCol, '$[6]') AS COLUMN7   
   ,JSON_VALUE(jsCol, '$[7]') AS COLUMN8   
   ,JSON_VALUE(jsCol, '$[8]') AS COLUMN9    
FROM CTE CROSS APPLY OPENJSON(jsCol) AS j  

Considering the situation of duplicate data proposed by @Ronen Ariely , I added the ID column using ROW_NUMBER inside the CTE.

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.

Answer 2

Hi @LiHong-MSFT

Thanks for your answer. Is there any other way instead of JSON query. Looks similar to XML but not if it will take less time or not.