How to Convert Semi colon Separated Values into Column

Question

Post reply

How to Convert Semi colon Separated Values into Column

joshtheflame

Default port

Points: 1499
More actions
March 6, 2013 at 10:29 pm

#271965

I am working on a integration project where I am receiving three string parameters ItemCode ItemName Amount
and values will be in this format
ItemCod Parameter values
T1;T2;T3;
ItemName Parameter values
Pencil Box;Eraser;Mouse Pad;
Amount Paramter values
1900;2000;8900;
Now I would like to have a procedure in which i would receive these parameters and the query will convert this as column and records. I am not a SQLServer guy so somebody proposed a solution but it is limited to two records only where my requirement is as many records depends on the number of semi colon separated strings. The defined structure is that all parameters will have equal number of values.
here is the solution somebody gave it to me.
INSERT INTO t
(cod, name)
VALUES
('T1;T2;T3;T4;T5;',
'Pencil Box;Eraser;Board Marker;Trimmer;Ball Point;');
SELECT
CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') cod,
CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') name
INTO #tmpTable FROM t
insert INTO #tmpTable
SELECT
CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') cod,
CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') name
FROM t;
select * from #tmpTable
Here is the helper script to create required table so it wont waste your valuable time
CREATE TABLE [dbo].[t](
[cod] [varchar](350) NULL,
[name] [varchar](300) NULL
) ON [PRIMARY]
GO

Viewing 7 posts - 1 through 6 (of 6 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

post the expected output based on your above data's example

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Hotblack SSC Veteran Points: 228 More actions · Answer 2

You could use a recursive cte to split the strings:

WITH rep AS

(

SELECT

cod

,';' delim

,name

from t

UNION ALL

SELECT

LEFT(cod, CHARINDEX(delim, cod, 1) - 1)

,delim

,LEFT(name, CHARINDEX(delim, name, 1) - 1)

FROM rep

WHERE (CHARINDEX(delim, cod, 1) > 0)

UNION ALL

SELECT

RIGHT(cod, LEN(cod) - CHARINDEX(delim, cod, 1))

,delim

,RIGHT(name, LEN(name) - CHARINDEX(delim, name, 1))

FROM rep

WHERE (CHARINDEX(delim, cod, 1) > 0)

)

SELECT

cod

,name

FROM rep

WHERE (CHARINDEX(delim, cod, 1) = 0)

AND LEN(cod)>0

OPTION (MAXRECURSION 0);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DECLARE @ItemCod VARCHAR(8000) = 'T1;T2;T3;'

DECLARE @ItemName VARCHAR(8000) = 'Pencil Box;Eraser;Mouse Pad;'

DECLARE @Amount VARCHAR(8000) = '1900;2000;8900;'

SELECT

c.ItemNumber,

ItemCod = c.Item,

ItemName = n.Item,

Amount = a.Item

FROM dbo.DelimitedSplit8K(@ItemCod,';') c

INNER JOIN dbo.DelimitedSplit8K(@ItemName,';') n ON n.ItemNumber = c.ItemNumber

INNER JOIN dbo.DelimitedSplit8K(@Amount,';') a ON a.ItemNumber = c.ItemNumber

WHERE c.Item <> ''

-- Results

ItemNumberItemCodItemNameAmount

1 T1 Pencil Box 1900

2 T2 Eraser 2000

3 T3 Mouse Pad 8900

The function DelimitedSplit8K is discussed in this article[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 4

Life will be much easier if you pass that as an XML parameter.

DECLARE @x XML =

'

<Items>

<Item>

<ItemCode>T1</ItemCode>

<ItemName>Pencil Box</ItemName>

<Amount>1900</Amount>

</Item>

<Item>

<ItemCode>T2</ItemCode>

<ItemName>Eraser</ItemName>

<Amount>2000</Amount>

</Item>

<Item>

<ItemCode>T3</ItemCode>

<ItemName>Mouse Pad</ItemName>

<Amount>8900</Amount>

</Item>

</Items>

';

SELECT

i.value('(ItemCode)[1]','varchar(50)'),

i.value('(ItemName)[1]','varchar(50)'),

i.value('(Amount)[1]','numeric')

FROM

@x.nodes('//Items/Item') TAB(i)

https://sqlroadie.com/

joshtheflame Default port Points: 1499 More actions · Answer 5

Chris...hats off to yaaaaaaaaaaaaaaaaa 🙂 so awesome and superb fast...I simply praised you for the awesome query 🙂 Wish I could learn SQL like ya 🙂

ChrisM@Work (3/7/2013)
DECLARE @ItemCod VARCHAR(8000) = 'T1;T2;T3;' DECLARE @ItemName VARCHAR(8000) = 'Pencil Box;Eraser;Mouse Pad;' DECLARE @Amount VARCHAR(8000) = '1900;2000;8900;' SELECT c.ItemNumber, ItemCod = c.Item, ItemName = n.Item, Amount = a.Item FROM dbo.DelimitedSplit8K(@ItemCod,';') c INNER JOIN dbo.DelimitedSplit8K(@ItemName,';') n ON n.ItemNumber = c.ItemNumber INNER JOIN dbo.DelimitedSplit8K(@Amount,';') a ON a.ItemNumber = c.ItemNumber WHERE c.Item <> '' -- Results ItemNumberItemCodItemNameAmount 1 T1 Pencil Box 1900 2 T2 Eraser 2000
3 T3 Mouse Pad 8900
The function DelimitedSplit8K is discussed in this article[/url].

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Gosh :blush: thanks! Hang around here, read the articles...you'll soon pick it up.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden