Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Convert Semi colon Separated Values into Column Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 10:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 1, 2014 4:22 PM
Points: 59, Visits: 203
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

Post #1427770
Posted Thursday, March 7, 2013 12:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1427806
Posted Thursday, March 7, 2013 1:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:03 AM
Points: 169, Visits: 104
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);




Post #1427831
Posted Thursday, March 7, 2013 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 7,234, Visits: 13,715
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
ItemNumber ItemCod ItemName Amount
1 T1 Pencil Box 1900
2 T2 Eraser 2000
3 T3 Mouse Pad 8900

The function DelimitedSplit8K is discussed in this article.


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427832
Posted Thursday, March 7, 2013 2:35 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:57 AM
Points: 406, Visits: 773
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)

Post #1427850
Posted Thursday, March 7, 2013 4:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 1, 2014 4:22 PM
Points: 59, Visits: 203
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
ItemNumber ItemCod ItemName Amount
1 T1 Pencil Box 1900
2 T2 Eraser 2000
3 T3 Mouse Pad 8900

The function DelimitedSplit8K is discussed in this article.
Post #1427897
Posted Thursday, March 7, 2013 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 7,234, Visits: 13,715
Gosh 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427913
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse