how to select multiple rows delimited with comma

  • I have a table like this :

    ID Description ParentID Level

    B.01 Parent 1 H

    B.01.01 Parent 1.1 B.01 H

    B.01.01.01 Parent 1.1.1 B.01.01 H

    B.01.01.01.01 Detail 1 B.01.01.01 D

    B.01.01.01.02 Detail 2 B.01.01.01 D

    B.02 Parent 2 H

    B.02.01 Parent 2.1 B.02 H

    B.02.01.01 Detail 1 B.02.01 D

    How to make SQL query to be the output like this ?

    ID Description

    B.01.01.01 Parent 1, Parent 1.1, Parent 1.1.1

    B.02.01 Parent 2, Parent 2.1

    That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.

    Thanks before and after..

  • DROP TABLE #Hierarchy

    CREATE TABLE #Hierarchy (ID VARCHAR(20), [Description] VARCHAR(20), ParentID VARCHAR(20), [Level] CHAR(1))

    INSERT INTO #Hierarchy (ID, [Description], ParentID, [Level])

    SELECT 'B.01','Parent 1',NULL,'H' UNION ALL

    SELECT 'B.01.01','Parent 1.1','B.01','H' UNION ALL

    SELECT 'B.01.01.01','Parent 1.1.1', 'B.01.01','H' UNION ALL

    SELECT 'B.01.01.01.01', 'Detail 1','B.01.01.01','D' UNION ALL

    SELECT 'B.01.01.01.02', 'Detail 2','B.01.01.01','D' UNION ALL

    SELECT 'B.02','Parent 2',NULL,'H' UNION ALL

    SELECT 'B.02.01','Parent 2.1','B.02','H' UNION ALL

    SELECT 'B.02.01.01','Detail 1','B.02.01','D'

    SELECT * FROM #Hierarchy WHERE [Level] = 'H';

    WITH rCTE AS (

    SELECT ID, Branch = [Description], [Description] = CAST([Description] AS varchar(100)), ParentID, [Level], l = 1

    FROM #Hierarchy

    WHERE [Level] = 'H' AND ParentID IS NULL

    UNION ALL

    SELECT h.ID, Branch = r.Branch, CAST(r.[Description] + ', ' + h.[Description] AS varchar(100)), h.ParentID, h.[Level], r.l+1

    FROM rCTE r

    INNER JOIN #Hierarchy h ON h.ParentID = r.ID AND h.[Level] = 'H'

    )

    SELECT ID, [Description]

    FROM (SELECT *, r = ROW_NUMBER() OVER(PARTITION BY Branch ORDER BY l DESC) FROM rCTE) d

    WHERE r = 1

    ORDER BY ID

    /*

    ID Description

    B.01.01.01 Parent 1, Parent 1.1, Parent 1.1.1

    B.02.01 Parent 2, Parent 2.1

    */

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • thanks brother..

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply