July 1, 2015 at 1:03 pm
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment](
[EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
July 1, 2015 at 1:23 pm
knakka99 (7/1/2015)
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment]([EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
Something like this:
select
e.[EQU],
ca1.ItemNumber Seq,
max(case when ca2.ItemNumber = 1 then ca2.Item else '' end) Col1,
max(case when ca2.ItemNumber = 2 then ca2.Item else '' end) Col2,
max(case when ca2.ItemNumber = 3 then ca2.Item else '' end) Col3,
max(case when ca2.ItemNumber = 4 then ca2.Item else '' end) Col4,
max(case when ca2.ItemNumber = 5 then ca2.Item else '' end) Col5,
max(case when ca2.ItemNumber = 6 then ca2.Item else '' end) Col6,
e.[Facility]
from
#Equipment e
cross apply (select * from dbo.DelimitedSplit8K(e.Notes,'~'))ca1
cross apply (select * from dbo.DelimitedSplit8K(ca1.Item,'^'))ca2
where
ca1.Item > ''
group by
e.[EQU],
ca1.ItemNumber,
e.[Facility]
order by
cast(e.[EQU] as int) desc,
ca1.ItemNumber,
e.[Facility];
The code for the delimited split function is attached.
July 2, 2015 at 7:44 am
Thank you for the query.. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply