Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Question Unanswered: Distinct merging multiple values from same field

    Hi all,

    I'd need to select and merge values (distinct) from the same field of multiple selected rows from the table B to join table A.

    <Table A>
    ID Name
    ----------
    1 John
    2 Susan

    <Table B>
    ParentID Asset
    ----------------
    1 house
    1 house
    1 car
    2 tv
    2 dvd

    When I do the intended join selection, I'd need to see the result below:

    ID Name Asset
    -------------------
    1 John house,car
    2 Susan tv, dvd

    The scenario has been bothering me quite a while, really need some help here.

    Please help with solution and script details on if this could be done with a single query or need of a stored procedure call.

    Thanks in advance,
    Steve

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server are you using?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    Quote Originally Posted by blindman View Post
    What version of SQL Server are you using?
    Hi blindman,

    Both 2005 and 2008.

    Thanks,
    Steve

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    I can't tell if this is a homework question or not, here's the pseudo code to get you going, just learn how to write a cursor and the rest is pretty simple, you'd also have to create a subquery to select DISTINCT on [Table B] since it appears you have duplicate data.

    Code:
    DECLARE @MyAssets nvarchar(max)
    SET @MyAssets = ''
    
    Write a cursor on a SELECT (DISTINCT) from [Table A]
    BEGIN
    	SELECT @MyAssets = @MyAssets + Asset + ', ' FROM [Table B] WHERE ID = @ParentID
    	SET @MyAssets = Left(@MyAssets, Len(@MyAssets) - 1)
    	SELECT @ParentID, @MyAssets -- put into a table if you want to select it all out at once after cursor completes
    END
    Last edited by Gagnon; 10-12-10 at 17:47.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In 2005 and up, you can do this in a single statement with no cursors by using a Common Table Expression.
    Code:
    declare	@TableA table (ID int, Name varchar(10))
    declare	@TableB table (ParentID int, Asset varchar(10))
    insert into @TableA (ID, Name)
    select	1, 'John'
    union select 2, 'Susan'
    insert into @TableB (ParentID, Asset)
    select 1, 'house'
    union select 1, 'house'
    union select 1, 'car'
    union select 2, 'tv'
    union select 2, 'dvd'
    
    ;with CTE as
    	(select	ParentID,
    		convert(varchar(500), min(Asset)) as AssetString,
    		min(Asset) as LastAssetUsed,
    		1 as AssetCount
    	from	@TableB
    	group by ParentID
    	UNION ALL
    	select	CTE.ParentID,
    		convert(varchar(500), CTE.Assetstring + ', ' + Asset) as AssetString,
    		Asset as LastAssetUsed,
    		AssetCount + 1 as AssetCount
    	from	CTE
    		inner join @TableB TableB on CTE.ParentID = TableB.ParentID
    	where   TableB.Asset > CTE.LastAssetUsed)
    select  TableA.ID,
    	TableA.Name,
            CTE.AssetString
    from    @TableA TableA
    	inner join CTE on TableA.ID = CTE.ParentID
            inner join
                   (select ParentID,
                          max(AssetCount) as AssetCount
                   from    CTE
                   group by ParentID) LongestStrings
                   on CTE.ParentID = LongestStrings.ParentID
                   and CTE.AssetCount = LongestStrings.AssetCount
    Another method, which is simpler to code, is to write a user-defined function that returns the concatenated string for any parent ID, and include it in a select from TableA.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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