Solved

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

Posted on 2008-10-31
5
523 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:endrec
  • 4
5 Comments
 
LVL 13

Expert Comment

by:sm394
ID: 22853599
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
0
 
LVL 13

Expert Comment

by:sm394
ID: 22853609
then

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

Author Comment

by:endrec
ID: 22853819
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
0
 
LVL 13

Accepted Solution

by:
sm394 earned 500 total points
ID: 22854037
CREATE FUNCTION [dbo].[fnDocumentIDs](@CustomerID INT)
RETURNS NVARCHAR(1000)
AS
BEGIN


DECLARE @DocIDs nvarchar(1000), @delimiter char
SET @delimiter = ','

SELECT     @DocIDs= COALESCE(@DocIDs+@delimiter,'')+ YourTable.DocumentID
FROM   YourTable
WHERE CustomerID=@CustomerID

RETURN RTRIM(LTRIM(@DocIDs))

END


--------------
next query will follow
0
 
LVL 13

Assisted Solution

by:sm394
sm394 earned 500 total points
ID: 22854065
select    CustomerName,
[dbo].[fnDocumentIDs](CustomerID) as ListofTop3Documents
from tblCustomers
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 36
SQL - Update field defined as Text 6 17
Create snapshot on MSSQL 2012 3 20
SQL - Use results of SELECT DISTINCT in a JOIN 4 20
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question