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
527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

732 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