none
Capture the the row identity ID by comma separated for same rows which is using of ROW_NUMBER() OVER (PARTITION BY RRS feed

  • Question

  •   Hi,

    My data like below:

    ID    Name     class    score

    ---  -------    --------    ----

    1     P           1           20

    2     P           1           20

    3     A           2          10

    4     S           3          20

    Now I need data like below:

     ID  Name     class    score    Internal_ID_by comma 

    ---  -------    --------    ----   ------------------------------

    1     P           1           20          1,2

    3     A           2          10           3

    4     S           3          20           4

    need on Capture the the row identity ID by comma separated for same rows which is using of ROW_NUMBER() OVER (PARTITION BY   to avoid duplicate row in table

    Could you please any help..,

    Thanks 

    Thursday, January 23, 2020 9:02 AM

Answers

  • Hi , 

    Please check following script. 

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    CREATE  TABLE  test
    (ID int,
    Name varchar(10),
    class int,
    score int )
    
    INSERT INTO test VALUES 
    (1,'P',1,20),
    (2,'P',1,20),
    (3,'A',2,10),
    (4,'S',3,20)
    
    ; with cte as (
    SELECT t1.Name,t1.class,t1.score,
    Stuff(( SELECT ',' + Cast(t2.ID as varchar(5))
    FROM test t2
    WHERE t2.Name = t1.Name and  t2.class = t1.class and t2.score = t1.score
    ORDER BY id
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [Internal_ID_by comma] 
    FROM test t1
    GROUP BY t1.ID,t1.Name,t1.class,t1.score)
    select distinct rank()over(order by [Internal_ID_by comma]) ID, * 
    from cte 
    /*
    ID                   Name       class       score       Internal_ID_by comma
    -------------------- ---------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1                    P          1           20          1,2
    3                    A          2           10          3
    4                    S          3           20          4
    
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sign of Shine Friday, January 24, 2020 1:37 PM
    Friday, January 24, 2020 6:05 AM
  • Working good thanks but instead of  above Query with more joins for multiple columns.

    Simplified  & Optimized the  using below Query by using dense_rank & partition by

    ; with cte as 
    (SELECT DENSE_RANK() OVER( ORDER BY Name,class,score) as Dense_RID,
    ROW_NUMBER() OVER (PARTITION BY Name,class,score ORDER BY Name,class,score) as Partition_RID,
    * from  test )
    select  
    Stuff( (SELECT ',' + CAST(ID AS VARCHAR) FROM cte WHERE  Dense_RID = A.Dense_RID  FOR XML PATH(''), TYPE) .value('.', 'varchar(max)'),1,1,'')  AS [IDS] 
    ,Name,class,score
    from cte A WHERE Partition_RID =1
    ORDER BY 1


    • Marked as answer by Sign of Shine Thursday, January 30, 2020 7:19 AM
    • Edited by Sign of Shine Thursday, January 30, 2020 7:20 AM spell
    Friday, January 24, 2020 1:40 PM

All replies

  • CREATE  TABLE  #mable(mid INT, token nvarchar(16))

    INSERT INTO #mable VALUES (1, 'p')
    INSERT INTO #mable VALUES(2, 'p')
    INSERT INTO #mable VALUES(3, 'a')
    INSERT INTO #mable VALUES(4, 's')


     SELECT 
            token,
            STUFF(group_list, 1, 1, '') AS groups
    FROM #mable AS A
    CROSS APPLY (SELECT ',' + cast(mid as varchar(10))
                  FROM #mable AS B
                  WHERE B.token = A.token
                  FOR XML PATH('') ) AS T(group_list) 
    group by token,STUFF(group_list, 1, 1, '')

    order by STUFF(group_list, 1, 1, '')  ;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, January 23, 2020 9:33 AM
    Answerer
  • Check this query too:

    select MIN(ID) as ID, class, [Name], score, STRING_AGG(ID, ',') as Internal_ID_by_comma
    from MyTable
    group by class, [Name], score
    order by class, [Name], score
    
    

    Thursday, January 23, 2020 10:02 AM
  • STRING_AGG() FUNCTION  is not support in  SQL 2016 version 
    Friday, January 24, 2020 5:44 AM
  • but had table had multiple columns(like ., tokens ) almost 15 so above suggested query is long .

    Is it possible with Partition by --Row_number/Dense_rank....??


    Friday, January 24, 2020 5:52 AM
  • Hi , 

    Please check following script. 

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    CREATE  TABLE  test
    (ID int,
    Name varchar(10),
    class int,
    score int )
    
    INSERT INTO test VALUES 
    (1,'P',1,20),
    (2,'P',1,20),
    (3,'A',2,10),
    (4,'S',3,20)
    
    ; with cte as (
    SELECT t1.Name,t1.class,t1.score,
    Stuff(( SELECT ',' + Cast(t2.ID as varchar(5))
    FROM test t2
    WHERE t2.Name = t1.Name and  t2.class = t1.class and t2.score = t1.score
    ORDER BY id
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [Internal_ID_by comma] 
    FROM test t1
    GROUP BY t1.ID,t1.Name,t1.class,t1.score)
    select distinct rank()over(order by [Internal_ID_by comma]) ID, * 
    from cte 
    /*
    ID                   Name       class       score       Internal_ID_by comma
    -------------------- ---------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1                    P          1           20          1,2
    3                    A          2           10          3
    4                    S          3           20          4
    
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sign of Shine Friday, January 24, 2020 1:37 PM
    Friday, January 24, 2020 6:05 AM
  • Working good thanks but instead of  above Query with more joins for multiple columns.

    Simplified  & Optimized the  using below Query by using dense_rank & partition by

    ; with cte as 
    (SELECT DENSE_RANK() OVER( ORDER BY Name,class,score) as Dense_RID,
    ROW_NUMBER() OVER (PARTITION BY Name,class,score ORDER BY Name,class,score) as Partition_RID,
    * from  test )
    select  
    Stuff( (SELECT ',' + CAST(ID AS VARCHAR) FROM cte WHERE  Dense_RID = A.Dense_RID  FOR XML PATH(''), TYPE) .value('.', 'varchar(max)'),1,1,'')  AS [IDS] 
    ,Name,class,score
    from cte A WHERE Partition_RID =1
    ORDER BY 1


    • Marked as answer by Sign of Shine Thursday, January 30, 2020 7:19 AM
    • Edited by Sign of Shine Thursday, January 30, 2020 7:20 AM spell
    Friday, January 24, 2020 1:40 PM