separate pipe values

• NewBornDBA2017

  SSCarpal Tunnel

  Points: 4832

  More actions

  September 11, 2018 at 3:01 pm

  #363045

  Is there a SQL to separate pipe values into separate rows? This is how the values are stored in my table 

  CompanyNid    Produce(Commodity)
  5836     Cooking Vegetables|Salad Vegetables|Berries|Citrus|Deciduous Fruit|Melons|Stone Fruit|Tropical Fruit
  5838     Onions,Green|Radishes|Cabbage|Broccoli|Carrots|Cantaloupe|Watermelon|Bok Choy|Napa Cabbage|Cooking Vegetables|Salad Vegetables

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 11, 2018 at 3:40 pm

  #2005344

  That's easy to do using a string splitter, of which the best is at the end of the following article:

  http://www.sqlservercentral.com/articles/72993/

  Here's the code to do it:
  CREATE TABLE #DATA (
    CompanyNid                int NOT NULL PRIMARY KEY CLUSTERED,
    [Produce(Commodity)]    varchar(200) NOT NULL
)
INSERT INTO #DATA
    (
    CompanyNid,
    [Produce(Commodity)]
    )
    VALUES    (5836, 'Cooking Vegetables|Salad Vegetables|Berries|Citrus|Deciduous Fruit|Melons|Stone Fruit|Tropical Fruit'),
            (5838, 'Onions,Green|Radishes|Cabbage|Broccoli|Carrots|Cantaloupe|Watermelon|Bok Choy|Napa Cabbage|Cooking Vegetables|Salad Vegetables');

  SELECT
    D.CompanyNid,
    S.Item AS [Produce(Commodity)],
    S.ItemNumber
FROM #DATA AS D
CROSS APPLY fnDelimitedSplit8K_LEAD(D.[Produce(Commodity)], '|') AS S
ORDER BY
    D.CompanyNid,
    S.ItemNumber;

  DROP TABLE #DATA;

  Be sure to read the article as it's golden.   You'll want the 2012 version, and I think the link to that is at the bottom of the page.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  September 11, 2018 at 3:47 pm

  #2005346

  I use something similar to the below for some ad-hoc scripts I have:

  
DECLARE @test-2 TABLE
(
  [CompanyNid] INT,
  [Produce(Commodity)] VARCHAR(MAX)
)

  INSERT INTO @test-2
VALUES
  (5836, 'Cooking Vegetables|Salad Vegetables|Berries|Citrus|Deciduous Fruit|Melons|Stone Fruit|Tropical Fruit'),
  (5838, 'Onions,Green|Radishes|Cabbage|Broccoli|Carrots|Cantaloupe|Watermelon|Bok Choy|Napa Cabbage|Cooking Vegetables|Salad Vegetables')

  DECLARE @Split char(1) = '|'

  SELECT
  t1.CompanyNid,
  t2.Commodity AS [Produce(Commodity)]
FROM
(
  SELECT [CompanyNid], CONVERT(xml,'<s>' + REPLACE([Produce(Commodity)],@Split,'</s><s>') + '</s>') AS CommodityXML FROM @test-2
) t1
CROSS APPLY
(
  SELECT ltrim(rtrim(T.c.value('.','varchar(100)'))) AS Commodity FROM t1.CommodityXML.nodes('/s') T(c)
) t2

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 12, 2018 at 9:47 am

  #2005450

  SQLPirate - Tuesday, September 11, 2018 3:47 PM

  I use something similar to the below for some ad-hoc scripts I have:

  
DECLARE @test-2 TABLE
(
  [CompanyNid] INT,
  [Produce(Commodity)] VARCHAR(MAX)
)

  INSERT INTO @test-2
VALUES
  (5836, 'Cooking Vegetables|Salad Vegetables|Berries|Citrus|Deciduous Fruit|Melons|Stone Fruit|Tropical Fruit'),
  (5838, 'Onions,Green|Radishes|Cabbage|Broccoli|Carrots|Cantaloupe|Watermelon|Bok Choy|Napa Cabbage|Cooking Vegetables|Salad Vegetables')

  DECLARE @Split char(1) = '|'

  SELECT
  t1.CompanyNid,
  t2.Commodity AS [Produce(Commodity)]
FROM
(
  SELECT [CompanyNid], CONVERT(xml,'<s>' + REPLACE([Produce(Commodity)],@Split,'</s><s>') + '</s>') AS CommodityXML FROM @test-2
) t1
CROSS APPLY
(
  SELECT ltrim(rtrim(T.c.value('.','varchar(100)'))) AS Commodity FROM t1.CommodityXML.nodes('/s') T(c)
) t2

  XML will slow this down considerably.   The string splitter will be considerably faster.   For this small volume, it might be hard to see, but if you timed it you could see the difference.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  September 12, 2018 at 10:21 am

  #2005459

  sgmunson - Wednesday, September 12, 2018 9:47 AM

  SQLPirate - Tuesday, September 11, 2018 3:47 PM

  I use something similar to the below for some ad-hoc scripts I have:

  
DECLARE @test-2 TABLE
(
  [CompanyNid] INT,
  [Produce(Commodity)] VARCHAR(MAX)
)

  INSERT INTO @test-2
VALUES
  (5836, 'Cooking Vegetables|Salad Vegetables|Berries|Citrus|Deciduous Fruit|Melons|Stone Fruit|Tropical Fruit'),
  (5838, 'Onions,Green|Radishes|Cabbage|Broccoli|Carrots|Cantaloupe|Watermelon|Bok Choy|Napa Cabbage|Cooking Vegetables|Salad Vegetables')

  DECLARE @Split char(1) = '|'

  SELECT
  t1.CompanyNid,
  t2.Commodity AS [Produce(Commodity)]
FROM
(
  SELECT [CompanyNid], CONVERT(xml,'<s>' + REPLACE([Produce(Commodity)],@Split,'</s><s>') + '</s>') AS CommodityXML FROM @test-2
) t1
CROSS APPLY
(
  SELECT ltrim(rtrim(T.c.value('.','varchar(100)'))) AS Commodity FROM t1.CommodityXML.nodes('/s') T(c)
) t2

  XML will slow this down considerably.   The string splitter will be considerably faster.   For this small volume, it might be hard to see, but if you timed it you could see the difference.

  No argument here, even just an addition of a few extra rows of similar data is laggy.