question

SQL9-3412 avatar image
0 Votes"
SQL9-3412 asked YitzhakKhabinsky-0887 edited

Parse single column into multiple columns using t-sql

Hi All,

I have a table with below data.
ID Notes
1 , Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active
2 , Date1: 01/12/2012 | Name: Danny | | | Status:
3 , Date1: 01/15/2012 | Name: Justin | | | Status: Active
4 , Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active
5 , Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |

The column Notes stores multiple(5-label/values) with pipeline separated. I want to split Notes column(5 label/values) into multiple columns.
I need output in below format:

ID Date1 Name Date2 Insurance Status
1 01/01/2012 John 01/31/2012 Yes Active
2 01/12/2012 Danny null null null
3 01/15/2012 Justin null null Active
4 01/21/2012 Kole 01/23/2012 null Active
5 01/21/2012 Duke 01/25/2012 null null


DECLARE @Data Table (ID int , Notes varchar(max))
Insert into @Data
Values
(1 , 'Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active'),
(2 , 'Date1: 01/12/2012 | Name: Danny | | | Status: ' ),
(3 , 'Date1: 01/15/2012 | Name: Justin | | | Status: Active'),
(4 , 'Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active'),
(5 , 'Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |')

Thanks in advance,
RH

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@SQL9-3412,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
HafeezUddin-8965 avatar image
0 Votes"
HafeezUddin-8965 answered SQL9-3412 commented

DECLARE @Data Table (ID int , Notes varchar(max))
Insert into @Data
Values
(1 , 'Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active'),
(2 , 'Date1: 01/12/2012 | Name: Danny | | | Status: ' ),
(3 , 'Date1: 01/15/2012 | Name: Justin | | | Status: Active'),
(4 , 'Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active'),
(5 , 'Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |')

--select * from @Data


;WITH MultipleColums
AS (
SELECT Notes
,CAST('<x>' + REPLACE(Notes, '|', '</x><x>') + '</x>' AS XML) AS Parts
FROM @Data
)
SELECT Notes
, Replace ( Parts.value(N'/x[1]', 'varchar(50)'), 'Date1: ','' ) AS [Date1]
,Replace (Parts.value(N'/x[2]', 'varchar(50)') , 'Name: ','' ) as [Name]
,Replace (Parts.value(N'/x[3]', 'varchar(50)') , 'Date2: ','' ) as [Date2]
,Replace (Parts.value(N'/x[4]', 'varchar(50)') , 'Insurance: ','' ) as Name
,Replace ( Parts.value(N'/x[5]', 'varchar(50)') , 'Status: ','' ) as Name
FROM MultipleColums;

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks HafeezUddin-8965 it worked as expected. Thank you for your help.

0 Votes 0 ·

I really appreciate your time and help on this post. Thanks again.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @SQL9-3412,

Your data resembles JSON, but it is tricky to convert to real JSON due to missing data between the pipes.

Here is a solution similar to @HafeezUddin-8965, just more performant and polished.
It is based on XML and XQuery.

SQL

 --DDL and sample data population, start
 DECLARE @tbl Table (ID INT IDENTITY PRIMARY KEY, Notes varchar(max))
 INSERT INTO @tbl (Notes) VALUES
 ('Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active'),
 ('Date1: 01/12/2012 | Name: Danny | | | Status: ' ),
 ('Date1: 01/15/2012 | Name: Justin | | | Status: Active'),
 ('Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active'),
 ('Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |');
 --DDL and sample data population, end
    
 ;WITH rs AS 
 (
  SELECT *
  , TRY_CAST('<root><x>' + REPLACE(Notes, '|', '</x><x>') + '</x></root>' AS XML) AS xmldata
  FROM @tbl
 )
 SELECT rs.ID
  , TRIM(REPLACE(c.value('(x[1]/text())[1]', 'VARCHAR(50)'), 'Date1:', '')) AS [Date1]
  , TRIM(REPLACE(c.value('(x[2]/text())[1]', 'VARCHAR(50)'), 'Name:', '')) AS [Name]
  , TRIM(REPLACE(c.value('(x[3]/text())[1]', 'VARCHAR(50)'), 'Date2:', '')) AS [Date2]
  , TRIM(REPLACE(c.value('(x[4]/text())[1]', 'VARCHAR(50)'), 'Insurance:', '')) AS Insurance 
  , TRIM(REPLACE(c.value('(x[5]/text())[1]', 'VARCHAR(50)'), 'Status:', '')) AS Status
 FROM rs
  CROSS APPLY xmldata.nodes('/root') AS t(c);

Output

 +----+------------+--------+------------+-----------+--------+
 | ID |   Date1    |  Name  |   Date2    | Insurance | Status |
 +----+------------+--------+------------+-----------+--------+
 |  1 | 01/01/2012 | John   | 01/31/2012 | Yes       | Active |
 |  2 | 01/12/2012 | Danny  | NULL       | NULL      |        |
 |  3 | 01/15/2012 | Justin | NULL       | NULL      | Active |
 |  4 | 01/21/2012 | Kole   | 01/23/2012 | NULL      | Active |
 |  5 | 01/21/2012 | Duke   | 01/25/2012 | NULL      | NULL   |
 +----+------------+--------+------------+-----------+--------+
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks YitzhakKhabinsky-0887 it worked as expected. Thank you for your help.

0 Votes 0 ·

I really appreciate your time and help on this post. Thanks again. Since the site allows only one user answer as accepted answer otherwise I would accept both answers.

0 Votes 0 ·