none
Grouping Data in a Table RRS feed

  • Question

  • Hi,

    I've a small sample table below.

    CREATE TABLE [dbo].[tab1](
     [ID] [nvarchar](255) NULL,
     [Name] [nvarchar](255) NULL,
     [Sports] [nvarchar](255) NULL
    ) ON [PRIMARY]

    insert tab1 values (1,'Tom','Boxing');
    insert tab1 values (1,'Tom','Jogging');
    insert tab1 values (1,'Tom','Reading');
    insert tab1 values (1,'May','Reading');
    insert tab1 values (1,'May','Cycling');
    insert tab1 values (1,'May','Swimming');

    ID Name Sports
    1 Tom Boxing
    1 Tom Jogging
    1 Tom Reading
    2 May Reading
    2 May Cycling
    2 May Swimming

    The below is my desired result. How do I achieve it ? Please enlighten me. TIA !

    ID Name Sports
    1 Tom Boxing
              Jogging
              Reading
    2 May Reading
              Cycling
              Swimming

    Thursday, June 22, 2017 2:44 AM

All replies

  • Are you trying to achieve this in SQL Management Studio, or as a report in SQL Reporting Services?

    Also, in the script provided the ID's for "May" are all 1 in the table population, however later identified as 2.  I'm guessing this was a typo/oversight?

    Thursday, June 22, 2017 3:40 AM
  • Hi,

    Typo. Selecting via SSMS.

    insert tab1 values (1,'Tom','Boxing');
    insert tab1 values (1,'Tom','Jogging');
    insert tab1 values (1,'Tom','Reading');
    insert tab1 values (2,'May','Reading');
    insert tab1 values (2,'May','Cycling');
    insert tab1 values (2,'May','Swimming');

    Thursday, June 22, 2017 3:47 AM
  • Hi limssd,

    The following code as shown uses ROW_NUMBER window function in the statement. The ROW_NUMBER window function Numbers the output of a result set. More specifically, it returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Please try to execute it and achieve your goal.

    CREATE TABLE [dbo].[tab1](
      [ID] [nvarchar](255) NULL,
      [Name] [nvarchar](255) NULL,
      [Sports] [nvarchar](255) NULL
     ) ON [PRIMARY]
    
    insert tab1 values (1,'Tom','Boxing');
     insert tab1 values (1,'Tom','Jogging');
     insert tab1 values (1,'Tom','Reading');
     insert tab1 values (2,'May','Reading');
     insert tab1 values (2,'May','Cycling');
     insert tab1 values (2,'May','Swimming');
     GO
    
    ;with cte as
    (
     select ROW_NUMBER() over (partition by ID order by Sports) as rn,
     ID,
     Name,
     Sports
     from tab1 
    )
    select case when T.rn=1 then T.ID else '' end as ID,case when T.rn=1 then T.Name else '' end as Name, Sports from CTE T
    order by T.ID,T.rn

    Hope above could be helpful to you.


    Best Regards,

    Will


    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.


    Thursday, June 22, 2017 4:09 AM
    Moderator
  • Hi Will,

    Excellent ! Thanks ! Another learning experience for me.

    Thursday, June 22, 2017 4:13 AM
  • Hi Will,

    Excellent ! Thanks ! Another learning experience for me.

    It's my pleasure. If you could mark the useful reply as answer, I would be highly appreciated. Thank you for your understanding.

    Best Regards,

    Will


    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.

    Thursday, June 22, 2017 8:14 AM
    Moderator
  • If you are using SQL Server 2012 or above, you can use below solution:

    CREATE TABLE [dbo].[tab1](
     [ID] [nvarchar](255) NULL,
     [Name] [nvarchar](255) NULL,
     [Sports] [nvarchar](255) NULL
    ) ON [PRIMARY]
    
    insert tab1 values (1,'Tom','Boxing');
    insert tab1 values (1,'Tom','Jogging');
    insert tab1 values (1,'Tom','Reading');
    insert tab1 values (2,'May','Reading');
    insert tab1 values (2,'May','Cycling');
    insert tab1 values (2,'May','Swimming');
    
    select case when ID <> Lag(ID) over(order by ID) or Lag(ID) over(order by ID) is null then ID else '' end as ID,
           case when ID <> Lag(ID) over(order by ID) or Lag(ID) over(order by ID) is null then Name else '' end as Name, sports from tab1
    • Edited by SQLNeophyte Thursday, June 22, 2017 9:11 AM
    Thursday, June 22, 2017 9:10 AM