Post reply

how to select multiple rows delimited with comma

  • October 6, 2015 at 3:34 am

    #305272

    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..

  • October 6, 2015 at 5:30 am

    #1831663

    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

  • October 7, 2015 at 12:20 am

    #1831915

    thanks brother..

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

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