How to SPLIT the Comma Seperate Values(CSV) in Sql Server 2005

Question

Post reply

How to SPLIT the Comma Seperate Values(CSV) in Sql Server 2005

Aswanth

Mr or Mrs. 500

Points: 548
More actions
June 16, 2008 at 11:41 pm

#189138

i'm having a Following table in Sql Server-2005...
( Emp_details --->Table Name)
slNo Emp_ID Emp_Name Emp_cat
1 1 acb 1,2,5,9
2 1 acb 4,7,8,9,10,15
3 2 mno 6,52,10,64,78
4 2 mno 7,9,5,8,1
5 3 xyz 1,2,8,7
6 3 xyz 3,5,7
7 3 xyz 2,4,6,87,4,31,46,7
Now I want the Stored Procedure in Such a way that ,
If i select the sl.No(Identity column), Emp_ID i want to get the Corresponding Emp_Cat values in Splitted format..
Like: I'm selecting sl.No=5 & Emp_ID=3
O/P:
slNo Emp_ID Emp_Cat
5 3 1
5 3 2
5 3 8
5 3 7
How to do this..
Please give me the Suggestions in doing this...
Thank You..

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

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

Master the basics, then go for the gold...

http://www.sqlservercentral.com/articles/TSQL/62867/

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Looks like an auxiliary table of numbers might do the trick, and I'm assuming that every possible value in the list for the Emp_cat field is numeric. Try this on for size:

-- Create an auxiliary table of numbers - method from T-SQL Querying by Itzik Ben-Gan

DECLARE @TALLY TABLE (N INT NOT NULL PRIMARY KEY)

DECLARE @max-2 AS INT, @rc AS INT

SET @max-2 = 99

SET @rc = 1

INSERT INTO @TALLY VALUES(1)

WHILE @rc * 2 <= @max-2

BEGIN

INSERT INTO @TALLY SELECT N + @rc FROM @TALLY

SET @rc = @rc * 2

END

INSERT INTO @TALLY

SELECT N + @rc FROM @TALLY WHERE N + @rc <= @max-2

DECLARE @EMP_DETAILS TABLE (

slNo int,

Emp_ID int,

Emp_Name varchar(20),

Emp_cat varchar(30)

)

INSERT INTO @EMP_DETAILS (slNo, Emp_ID, Emp_Name, Emp_cat)

SELECT 1,1,'acb','1,2,5,9' UNION ALL

SELECT 2,1,'acb','4,7,8,9,10,15' UNION ALL

SELECT 3,2,'mno','6,52,10,64,78' UNION ALL

SELECT 4,2,'mno','7,9,5,8,1' UNION ALL

SELECT 5,3,'xyz','1,2,8,7' UNION ALL

SELECT 6,3,'xyz','3,5,7' UNION ALL

SELECT 7,3,'xyz','2,4,6,87,4,31,46,7'

SELECT slNo, Emp_ID, N AS Emp_cat

FROM @EMP_DETAILS AS E, @TALLY AS T

WHERE slNo = 5 AND Emp_ID = 3 AND (

E.Emp_cat = LTRIM(RTRIM(CAST(N AS varchar(2)))) OR

E.Emp_cat LIKE LTRIM(RTRIM(CAST(N AS varchar(2)))) + ',%' OR

E.Emp_cat LIKE '%,' + LTRIM(RTRIM(CAST(N AS varchar(2)))) + ',%' OR

E.Emp_cat LIKE '%,' + LTRIM(RTRIM(CAST(N AS varchar(2))))

)

ORDER BY CHARINDEX(LTRIM(RTRIM(CAST(N AS varchar(2)))),Emp_cat)

The order by clause will get the rows in order of appearance in the original Emp_cat string. If that's irrelevant, then eliminate the order by clause. The last 4 parts of the WHERE clause are to find the numeric value as either the only item in the list, at the beginning of the list, in the middle of the list, or at the end of the list.

Steve

(aka smunson)

:):):)

Aswanth (6/16/2008)
i'm having a Following table in Sql Server-2005...
( Emp_details --->Table Name)
slNo Emp_ID Emp_Name Emp_cat
1 1 acb 1,2,5,9
2 1 acb 4,7,8,9,10,15
3 2 mno 6,52,10,64,78
4 2 mno 7,9,5,8,1
5 3 xyz 1,2,8,7
6 3 xyz 3,5,7
7 3 xyz 2,4,6,87,4,31,46,7
Now I want the Stored Procedure in Such a way that ,
If i select the sl.No(Identity column), Emp_ID i want to get the Corresponding Emp_Cat values in Splitted format..
Like: I'm selecting sl.No=5 & Emp_ID=3
O/P:
slNo Emp_ID Emp_Cat
5 3 1
5 3 2
5 3 8
5 3 7
How to do this..
Please give me the Suggestions in doing this...
Thank You..

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

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Well... I just realized that your last example data breaks my query's sort mechanism, and also doesn't have the 2nd occurrence of 4 in the list show up in the output, and to boot, the sort order can't deal with a single digit number appearing in the list and also being the 2nd digit of a 2 digit number in the list. However, from a practical perpsective, is that needed? Would an employee need to appear more than once in a given category? It would seem unlikely, but let us know...

Steve

(aka smunson)

:):):)

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