t-sql split comma delimited rows : The Official Microsoft ASP.NET Forums

Reply

t-sql split comma delimited rows

Feb 18, 2012 06:37 AM|LINK

how can i get this output with stored procedure.

Id	AllNames
1	A,B,C
2	A,B
3	X,Y,Z

output

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

Reply

Re: t-sql split comma delimited rows

Feb 18, 2012 06:42 AM|LINK

For example, in stead of:

ProjectID Label
———— ——–
1200 label1
1200 label2
1200 label3

I would like the result of my query to look like this:
ProjectID Label
———— ——–
1200 label1, label2, label3

In SQL Server 2000 this isn’t such a straightforward thing to do. After some googling, I came across a very useful forumthat helped me out. I can use a function to return the list of labels:

USE myDatabaseName
GO
CREATE FUNCTION dbo.ConcatLabels(@projectID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+’, ‘, ”) + CONVERT(varchar(20), b.label_name)
FROM dbo.[T_labelproject] a
JOIN dbo.T_label b ON b.labelID = a.labelID
WHERE a.projectID = @projectID
ORDER BY b.label_Name
RETURN @Output
END
GO

Query it with:
SELECT projectID, dbo.ConcatLabels(projectID) AS Label FROM T_Project

MCTS
If you feel it helps, Mark as answered so that it can help others to find solution.
For Any further questions, please contact me.

Edited by muhammad fakhr elden sami on Feb 18, 2012 06:42 AM

Reply

yrb.yogi

Star

14460 Points

2402 Posts

Re: t-sql split comma delimited rows

Feb 18, 2012 06:42 AM|LINK

--first of all create this function
--

Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
	declare @idx int
	declare @slice varchar(8000)
    
	select @idx = 1
		if len(@String)<1 or @String is null  return
    
	while @idx!= 0
	begin
		set @idx = charindex(@Delimiter,@String)
		if @idx!=0
			set @slice = left(@String,@idx - 1)
		else
			set @slice = @String
		
		if(len(@slice)>0)
			insert into @temptable(Items) values(@slice)

		set @String = right(@String,len(@String) - @idx)
		if len(@String) = 0 break
	end
return
end



GO
--now run your query as below

declare @table table
(
	Id int,AllNames varchar(100)
)
insert into @table
select 1,'A,B,C' union all
select 2,'A,B'  union all
select 3,'X,Y,Z'

select Id,items as Name
from @table
CROSS APPLY dbo.Split(AllNames,',')

Yogesh Bhadauriya
.Net All About
MCC

Marked as answer by Chen Yu - MSFT on Feb 24, 2012 01:44 AM

Reply

rabindra_lal

Member

515 Points

202 Posts

Re: t-sql split comma delimited rows

Feb 18, 2012 07:02 AM|LINK

create FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(4000),
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (RowID   smallint IDENTITY(1,1), Item VARCHAR(50))
AS

BEGIN
    DECLARE @tempItemList NVARCHAR(4000)
    SET @tempItemList = @ItemList

    DECLARE @i INT
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)
      Declare @RowId Nvarchar(200)
    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END
    RETURN
END

select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

please mark as answer

Reply

serhatergun

Member

65 Points

118 Posts

Re: t-sql split comma delimited rows

Feb 18, 2012 04:48 PM|LINK

i have 200 records in my table. i gave the A,B,C as a sample.

1 A,B,C
2 A,B
3 X,Y,Z
4 D, F , G
5 . , . , .
6 . , . , .
7 . , . , .
8 . , . , .
9 . , . , .
    . , . , .
    .   . , . , .
200    .   . , . , .

FOR THIS ONLY  CERTAIN VARIABLES.   select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',') HOW CAN I MAKE FOR ALL ROWS?

1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3 4 4 4	Z D F G

Reply

me_ritz

Star

9337 Points

1447 Posts

Re: t-sql split comma delimited rows

Feb 18, 2012 05:05 PM|LINK

--Consider this your table with multiple rows(may be 200 rows)
--it is just created for demonstration

WITH TBL AS
(
  SELECT '1' as col1, 'A,B,C' as col2
  UNION
  SELECT '2' as col1, 'A,B' as col2
  UNION
  SELECT '3' as col1, 'X,Y,Z' as col2
  UNION
  SELECT '4' as col1, 'D,F,G' as col2
)

--Run this query on your table replacing column names with yours
SELECT col1, Tags.val.value('.', 'VARCHAR(MAX)') AS col2
FROM(
  SELECT  col1, CAST('<t>' + REPLACE(col2, ',', '</t><t>') + '</t>' AS XML) AS TAG
  FROM  TBL
) TAB CROSS APPLY TAG.nodes('/t') as Tags(val)

Please mark the post as "Answer" that helps you.

Reply

serhatergun

Member

65 Points

118 Posts

Re: t-sql split comma delimited rows

Feb 18, 2012 10:24 PM|LINK

i found it this url
http://stackoverflow.com/questions/3284426/splitting-comma-delimited-cell-data
for this table, this code works.

owners
=================
owner_id int,
parcelID int = autonumber
sample row (owner_id = '13482, 21461', parcelID = 318)

sample row (owner_id = '13782, 21431', parcelID = 319)

DECLARE @STR VARCHAR(MAX)

SET @STR = (SELECT DISTINCT STUFF( (SELECT ',' + owner_ID from owners FOR XML PATH('')),1,1,'') FROM owners)

SELECT * FROM [dbo].[Split](@STR,','),owners

output__
1 13482
2 21461
3 13782
4 21431

but i wanna output is such that

parcelID = 318 ownerID =13482
parcelID = 318 ownerID =21431
parcelID = 319 ownerID =13782
parcelID = 319 ownerID =21461

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

Marked as answer by Chen Yu - MSFT on Feb 24, 2012 01:44 AM

Reply

sandeepmitta...

Contributor

6767 Points

1057 Posts

Re: t-sql split comma delimited rows

Feb 19, 2012 03:08 AM|LINK

/*
CREATE FUNCTION dbo.Split(
  @delimited NVARCHAR(MAX),
  @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END
*/

DECLARE @TAB TABLE (Id INT, AllNames VARCHAR(100))
INSERT INTO @TAB
SELECT 1, 'A,B,C'
UNION ALL SELECT 2, 'A,B'
UNION ALL SELECT 3, 'X,Y,Z'

SELECT A.id, B.val
FROM @TAB A
CROSS APPLY dbo.Split(AllNames, ',') B