Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    72

    Unanswered: How to put a multiple result one-to-many query in one output field

    Hi,

    I am looking for a solution to put the results of a one-to-many query, where the results have an output in one field as shown below:

    Table A

    Product
    Prod1
    Prod2
    Prod3
    Prod4

    Table B

    Product Link
    Prod1 Link1
    Prod1 Link2
    Prod1 Link3
    Prod2 Link4
    Prod2 Link5
    Prod3 Link6
    Prod4 Link7
    Prod4 Link8

    Query Result:

    Product Links
    Prod1 Link1|Link2|Link3
    Prod2 Link4|Link5
    Prod3 Link6
    Prod4 Link7|Link8

    I know how to do it in Oracle with cursors, but not in SQL Server.
    Can anyone help me with this please?

    Kind regards and thanks in advance!

    Wim Venema

  2. #2
    Join Date
    Oct 2007
    Posts
    154
    Provided Answers: 9
    Try pivot.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,595
    Provided Answers: 1
    This recursive CTE will work as long as you don't reach the recursion limit of 100 Links for any Product.

    Code:
    set nocount on
    
    declare	@Table table (Product varchar(50), Link varchar(50))
    insert into @Table values ('Prod1', 'Link1')
    insert into @Table values ('Prod1', 'Link2')
    insert into @Table values ('Prod1', 'Link3')
    insert into @Table values ('Prod2', 'Link4')
    insert into @Table values ('Prod2', 'Link5')
    insert into @Table values ('Prod3', 'Link6')
    insert into @Table values ('Prod4', 'Link7')
    insert into @Table values ('Prod4', 'Link8')
    
    select	*
    from	@Table
    
    ;with	OrderedData as
    			(select	Product,
    					Link,
    					ROW_NUMBER() over (Partition by Product order by Link asc) as SortOrder,
    					ROW_NUMBER() over (Partition by Product order by Link desc) as ReverseOrder
    			from	@Table),
    		LinkStrings as
    			(select	OrderedData.Product,
    					convert(varchar(500), OrderedData.Link) as Link,
    					OrderedData.SortOrder,
    					OrderedData.ReverseOrder
    			from	OrderedData
    			where	SortOrder = 1
    			union all
    			select	OrderedData.Product,
    					convert(varchar(500), LinkStrings.Link + '|' + OrderedData.Link) as Link,
    					OrderedData.SortOrder,
    					OrderedData.ReverseOrder
    			from	LinkStrings
    					inner join OrderedData 
    						on LinkStrings.Product = OrderedData.Product
    						and LinkStrings.SortOrder = OrderedData.SortOrder - 1)
    select	Product,
    		Link
    from	LinkStrings
    where	ReverseOrder = 1
    order by Product
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    72
    Hi Blindman,

    Thanks for the solution, this works but....not on SQL2000 (sorry, still using it because of the 1000's of DTSses).
    Is there a way to use cursors like in Oracle?

    Rgds,

    Wim Venema

  5. #5
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    59
    You can actually do this with just Table B

    Code:
    SELECT DISTINCT N.PROD,
    	RTRIM(SUBSTRING(
    	(
    		SELECT '|' + N2.LNK + ' '
    		FROM TableB N2
    		WHERE N.PROD = N2.PROD
    		FOR XML PATH ('')
    	), 2, 1500)) AS LNK
    FROM TableB N

  6. #6
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    72
    It doesn't work in this way.
    Error message:

    Server: Msg 170, Level 15, State 1, Line 7
    Line 7: Incorrect syntax near 'XML'.

    Are you sure this will work on SQL2000?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •