August 21, 2016 at 12:59 pm
Given this:
DECLARE @t TABLE(ID INT IDENTITY, [product] VARCHAR(50), [sku] varchar(50), [colour] VARCHAR(50), [cm] VARCHAR(50), VARCHAR(50), [sm] VARCHAR(50));
INSERT INTO @t([product],[sku],[colour],[cm],,[sm])
VALUES ('Tee Shirt','TEE','Red,Yellow,Green,Blue', '-R,-Y,-G,-B', 'Small,Medium,Large', '-S,-M,-L')
,('Shorts','TRUNK','Orange,Fuschia,Magenta', '-O,-F,-M', '','')
,('Bottle','H2O','','','Pint,Litre','-10,-1000')
,('Bench','B10','','','','');
I want this output, using something like the popular DelimitedSplit8K(string,delimiter) function:
[ProductName] [Code]
Tee Shirt (Red,Small) TEE-R-S
Tee Shirt (Red,Medium) TEE-R-M
Tee Shirt (Red,Large) TEE-R-L
Tee Shirt (Yellow,Small) TEE-Y-S
Tee Shirt (Yellow,Medium) TEE-Y-M
Tee Shirt (Yellow,Large) TEE-Y-L
Tee Shirt (Green,Small) TEE-G-S
Tee Shirt (Green,Medium) TEE-G-M
Tee Shirt (Green,Large) TEE-G-L
Tee Shirt (Blue,Small) TEE-B-S
Tee Shirt (Blue,Medium) TEE-B-M
Tee Shirt (Blue,Large) TEE-B-L
Shorts (Orange) TRUNK-O
Shorts (Fuschia) TRUNK-F
Shorts (Magenta) TRUNK-M
Bottle (Pint) H2O-10
Bottle (Litre) H2O-1000
Bench B10
I split the 2 result columns into not bold and bold.
I'm sure you can see what I'm trying to do here. I have 4 columns in a table which contain csv data. They are in 2 pairs of 2. They may or may not have data. But if they do, in their pairs, they'll have equal number of elements. So (One,Two,Three) will have (1,2,3).
I've tried all sorts of combinations of CROSS APPLY or JOINs but can't seem to get it. I could do it using cursors, but that defeats the performance.
Any help greatly appreciated. Many thanks.
August 21, 2016 at 1:44 pm
This should work, but I really don't like your input data set. You are showing the text for colors AND their codes, the sizes AND their codes. Fortunately, the text and codes are in the same sequence so the WHERE clause works. If that sequence is ever broken, the Product column is not going to match the Code.
select replace(T.Product + ' (' + clr.item
+case when clr.item > '' and sz.item > '' then ',' else '' end
+sz.item+ ')'
,'()','') as Product
,SKU+cm1.item+sm1.Item as [Code]
from @t t
cross apply delimitedsplit8k(colour,',') clr
cross apply delimitedsplit8k(size,',') sz
cross apply delimitedsplit8k(cm,',') cm1
cross apply delimitedsplit8k(sm,',') sm1
where clr.itemnumber = cm1.itemnumber
and sz.itemnumber = sm1.itemnumber
You would be much better served by adding tables that contain both the codes and their text. For example
Create Table Sizes (SizeCode char(1) primary key, Size Varchar(10)
Insert Into Sizes
Values ('S','Small'),('M','Medium'),('L','Large')
Then your input could just be SKU, SizeCode, and ColorCode. The formula SKU+'-'+SizeCode+'-'+ColorCode would give the values for your [Code] result column and Joins would give you the elements of the full text for your [Product] result column. When you have some time, read up on normalization. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply