Parsing multiple key-value pair columns at once

• chintan.floydian

  SSC Veteran

  Points: 241

  More actions

  September 19, 2016 at 4:43 am

  #149284

  Hi, I have a table where the data has been loaded in this format (I know it should have been parsed and normalised at source but its not possible to change now since there are lot of interdependencies)

  I want to write a function (or sp) which can parse this. - without looping if possible

  DECLARE @T TABLE

  (

  CONTINENTID INT,

  COUNTRIES VARCHAR(255),

  CITIES VARCHAR(255)

  )

  INSERT INTO @T

  ( CONTINENTID, COUNTRIES, CITIES )

  VALUES ( 1, -- ID - int

  'INDIA,CHINA,BANGLADESH', -- COUNTRIES - varchar(255)

  'MUMBAI,DELHI|BEIJING,SHANGHAI|DHAKA' -- CITIES - varchar(255)

  )

  INSERT INTO @T

  ( CONTINENTID, COUNTRIES, CITIES )

  VALUES ( 2, -- ID - int

  'SPAIN,UK', -- COUNTRIES - varchar(255)

  'BARCELONA|LONDON,MANCHESTER' -- CITIES - varchar(255)

  )

  SELECT * FROM @T

  Expecting output like this

  CONTINENTIDCOUNTRY CITY

  1INDIAMUMBAI

  1INDIADELHI

  1CHINABEIJING

  1CHINASHANGHAI

  1BANGLADESHDHAKA

  2SPAINBARCELONA

  2UKLONDON

  2UKMANCHESTER

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  September 19, 2016 at 5:32 am

  #1901603

  Using the splitter here http://www.sqlservercentral.com/articles/Tally+Table/72993/

  SELECT t.CONTINENTID,

  s1.Item AS COUNTRY,

  s3.Item AS CITY

  FROM @T t

  CROSS APPLY dbo.DelimitedSplit8K(t.COUNTRIES,',') s1

  CROSS APPLY dbo.DelimitedSplit8K(t.CITIES,'|') s2

  CROSS APPLY dbo.DelimitedSplit8K(s2.Item,',') s3

  WHERE s1.ItemNumber = s2.ItemNumber

  ORDER BY t.CONTINENTID,s1.ItemNumber,s3.ItemNumber;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537