asked on

How to use a loop or a cursor to list values in a horizontal delimited string (e.g. 1001, 1002, 1003)?

I am trying to find some help/a way to output a horizontal comma delimited list of associated items in a table.

Example: A user wants to see a few documents tied to each company in a database for verification that the document have the correct company name listed.

DocumentID      CustomerID
1001            1
1002            1
1003            1
1004            1
1005            1
1006            2

CustomerID      Customer Name
1            IBM
2            National, Inc.

Customer Name      ListofTop3Documents
IBM            1001, 1002, 1003
National, Inc.            1006

Faizan Sarwar

CREATE FUNCTION [dbo].[fnSplit]
(
      -- Add the parameters for the function here

      @list nvarchar(2000),
      @delimiter nvarchar(5)

)
RETURNS
@output TABLE
(
      -- Add the column definitions for the TABLE variable here

data VARCHAR(256)
)
AS
BEGIN
      -- Fill the table variable with the rows for your result set

DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @list)

WHILE @start < LEN(@list) + 1
      BEGIN
IF @end = 0
SET @end = LEN(@list) + 1

INSERT INTO @output (data)
VALUES(SUBSTRING(@list, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @list, @start)
END

      RETURN
END

Faizan Sarwar

then

select * from dbo.fnSplit('1001, 1002, 1003',',')

endrec

ASKER

Oh, this is the end result that it desired (using the first two tables as the data sources):

Customer Name ListofTop3Documents
IBM 1001, 1002, 1003
National, Inc. 1006

ASKER CERTIFIED SOLUTION

Faizan Sarwar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Faizan Sarwar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial