If there is a table called variant

id             slug
-------        ---------
1              s
1              m
1              l
1              black
1              pink
2              xl
2              xxl
2              blue
2              white

Result I need in this format:

id             variant_slug
--------       ------------
1              1-s-black
1              1-s-pink
1              1-m-black
1              1-m-pink
1              1-l-black
1              1-l-pink
2              2-xl-blue
2              2-xl-white
2              2-xxl-blue
2              2-xxl-white

Q: this record is in same variant table. I have almost no experience using UDFs, stored procedures, I need to be done this thing through query. Is this possible without using UDFs, SP's.

CodePudding user response：

Perhaps a self-join assuming the length of size<=3.

Select A.ID
      ,variant_slug = concat(A.id,'-',A.slug,'-',B.slug)
 from  YourTable A
 Join  YourTable B 
   on A.ID=B.ID 
  and A.slug<>B.slug 
  and len(B.slug)>3 
  and len(A.slug)<=3

Results

ID  variant_slug
1   1-l-black
1   1-l-pink
1   1-m-black
1   1-m-pink
1   1-s-black
1   1-s-pink
2   2-xl-blue
2   2-xl-white
2   2-xxl-blue
2   2-xxl-white

Rather than len(), you can enumerate the sizes i.e. {NOT } IN ('s','m','l','xl','xxl')

Select A.ID
      ,variant_slug = concat(A.id,'-',A.slug,'-',B.slug)
 from  YourTable A
 Join  YourTable B 
   on A.ID=B.ID 
  and A.slug<>B.slug 
  and B.slug NOT IN ('s','m','l','xl','xxl') 
  and A.slug IN ('s','m','l','xl','xxl')

CodePudding user response：

You can go for INNER JOIN by separating the table into two separate tables: size, color and get the variant data, as given below:

declare @slug table(id int, slug varchar(100))

insert into @slug values
(1,'s')
,(1,'m')
,(1,'l')
,(1,'black')
,(1,'pink')
,(2,'xl')
,(2,'xxl')
,(2,'blue')
,(2,'white');

SELECT size.id, CONCAT_WS('-',size.id,size.size, color.color) as variant_size
from (select id,slug as size from @slug where slug in ('s','m','l','xl','xxl') ) as size
INNER JOIN (select id,slug as color from @slug where slug NOT in ('s','m','l','xl','xxl') ) as COLOR
on color.id = size.id