I have below table name: CustomerContent with the below image data and My product is:
is common in ProductContent cell. First colon no need to split (My product is:
) if next colons we need to split the ProductContent Cell base text assign the values like below. If slip data content CGM
then assign value 37.
CustomerId ProductContent
100 My product is: Shoes
101 My product is: Diabetic Shoes
102 My product is: Shoes Back Brace
103 My product is: Dexcom G6 (CGM)
104 My product is: Freestyle Libre (CGM)
105 My product is: Shoes Knee Brace
106 My product is: Dexcom G6 (CGM): Freestyle Libre (CGM): Diabetic Shoes
107 My product is: Dexcom G6 (CGM): Freestyle Libre (CGM)
108 My product is: Freestyle Libre (CGM): Diabetic Shoes
I need output like below and insert above data into another table name: CustomerContentTemp contain columns CusmerId
and Values
like below format.
CustomerId Values
100 1
101 1
102 8
103 37
104 37
105 14
106 37
106 37
106 1
107 37
107 37
108 37
108 1
From below data logic for inserting into output CustomerContentTemp table
Shoes=1
Diabetic Shoes=1
Shoes Back Brace=8
Dexcom G6 (CGM)=37
Freestyle Libre (CGM)=37
Shoes Knee Brace=14
IF ProductContent cell data is not match then insert value 0.
CodePudding user response:
My personal choice for a t-sql splitter is this one. https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 There are plenty of other choices and many have advantages and disadvantages. This one for me is the most robust with the least overhead and confusion. To find the code yourself you will need to scroll to the bottom of the article (ideally reading the whole thing so you understand what it is doing).
Now let's take your data and make it consumable so anybody can just copy and paste it. This article (How Stuff and 'For Xml Path' work in SQL Server?) will walk you through that part of the process.
create table CustomerContent
(
CustomerId int
, ProductContent varchar(500)
)
insert CustomerContent
select 100, 'My product is: Shoes' union all
select 101, 'My product is: Diabetic Shoes' union all
select 102, 'My product is: Shoes Back Brace' union all
select 103, 'My product is: Dexcom G6 (CGM)' union all
select 104, 'My product is: Freestyle Libre (CGM)' union all
select 105, 'My product is: Shoes Knee Brace' union all
select 106, 'My product is: Dexcom G6 (CGM): Freestyle Libre (CGM): Diabetic Shoes' union all
select 107, 'My product is: Dexcom G6 (CGM): Freestyle Libre (CGM)' union all
select 108, 'My product is: Freestyle Libre (CGM): Diabetic Shoes'
create table CustomerContentTemp
(
SimpleName varchar(100)
, Result int
)
insert CustomerContentTemp
select 'Shoes', 1 union all
select 'Diabetic Shoes', 1 union all
select 'Shoes Back Brace', 8 union all
select 'Dexcom G6 (CGM)', 37 union all
select 'Freestyle Libre (CGM)', 37 union all
select 'Shoes Knee Brace', 14
OK. So now have nice and easy to work with data and a splitter function. Here is the first step in your puzzle. You have to split this data apart and get rid of that oddball prefix.
select c.*
, cct.SimpleName
, cct.Result
from CustomerContent c
cross apply dbo.DelimitedSplit8K_LEAD(replace(c.ProductContent, 'My product is: ', ''), ':') x
left join CustomerContentTemp cct on cct.SimpleName = ltrim(x.Item)
From this result you will need to apply the logic for using STUFF to get this squished back into the denormalized format you need.