SQL Query Help

Question

Post reply

SQL Query Help

rocky_498

SSCertifiable

Points: 6532

April 13, 2022 at 6:42 pm

#4015275

Hi,

Below is my sample data. What I am looking, if "product_descr" has more than one value separated with (;) then create a new one with the same information except for the new "product_descr."

CREATE TABLE ##Data
   (
      ID       int IDENTITY(1, 1)
    , FullName varchar(50)
    , City     varchar(15)
, product_descrvarchar(500)
   );
GO

INSERT INTO ##Data
   (
      FullName
    , City
,product_descr
   )
VALUES
   ( 'Mr John Smith', 'Chicago','CM123;CM985;CM258' )
 , ( 'Mr.Smith', 'New York','CM856' )
 , ( 'ACME Social Club', 'Los Angles','' );

 SELECT * FROM ##Data d

Here is the result that I am expecting.

Thanks in advance for your help.

-R

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

You can use STRING_SPLIT to turn the delimited data into separate rows.

You can use CROSS APPLY (if product_descr is never null), or OUTER APPLY (if there are nulls) after your from statement to turn the delimited product_descr into a table expression for each person/city -- e.g.,

OUTER APPLY STRING_SPLIT(product_descr,';') products

SELECT the other columns as you normally would, & select products.value AS product_descr -- or whatever alias you choose.

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Hi, ratbak,

Thanks for your help. It worked.

 SELECT d.id,d.fullName,d.city,products.value
FROM ##Data d
     OUTER APPLY string_split(product_descr,';') products

"Answered"