t-sql split comma delimited rows

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

Re: t-sql split comma delimited rows

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 isnt 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

Re: t-sql split comma delimited rows

--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,',')

Re: t-sql split comma delimited rows

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

Re: t-sql split comma delimited rows

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

Re: t-sql split comma delimited rows

--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)

Re: t-sql split comma delimited rows

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

Re: t-sql split comma delimited rows

/*
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

t-sql split comma delimited rows

t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Re: t-sql split comma delimited rows

Split CSV string in a Table