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