locked
How to remove duplicated data in result set row RRS feed

  • Question

  • User2033107836 posted
    My Resultset Look Like This
    Mango 50 A 2 1
    Mango 50 B 10 5
    Mango 50 C 4 10
    Mango 50 D 6 5
    Apple 100 H 2 1
    Apple 100 K 4 5
    Apple 100 L 6 3
    Apple 100 N 1 2

    I want The Output Like This
    Mango 50 A 2 1
    " " B 10 5
    " " C 4 10
    " " D 6 5
    Apple 100 H 2 1
    " " K 4 5
    " " L 6 3
    " " N 1 2

    How To Do So..?

    Thank You

    Tuesday, November 8, 2016 7:40 PM

Answers

  • User-183374066 posted

    Change Field2 from float to varchar

    DECLARE @result TABLE
        (
          Field1 VARCHAR(100) ,
          Field2 VARCHAR(100) ,
          Field3 VARCHAR(100) ,
          Field4 VARCHAR(100) ,
          Field5 VARCHAR(100)
        );

    and a check inside while which insert first two values only for the first time

     WHILE @subRow <= ( SELECT   COUNT(*)
                               FROM     @t3
                             )
                BEGIN
    
                    IF ( @subRow = 1 )
                        BEGIN
                            INSERT  INTO @result
                                    ( Field1 ,
                                      Field2 ,
                                      Field3 ,
                                      Field4 ,
                                      Field5
                                    )
                            VALUES  ( @Field1 ,
                                      @Field2 ,
                                      ( SELECT  Data
                                        FROM    @t3
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t4
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t5
                                        WHERE   Id = @subRow
                                      )
                                    );
                        END;
                    ELSE
                        BEGIN
                            INSERT  INTO @result
                                    ( Field1 ,
                                      Field2 ,
                                      Field3 ,
                                      Field4 ,
                                      Field5
                                    )
                            VALUES  ( '' ,
                                      '' ,
                                      ( SELECT  Data
                                        FROM    @t3
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t4
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t5
                                        WHERE   Id = @subRow
                                      )
                                    );
                        END;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 8, 2016 8:48 PM

All replies

  • User77042963 posted

    You should format your data from your front end, not in your query. But here I how you have to do it in TSQL but a a blank string as you asked.

    The Field2 column has null value (you cannot put a string into a float  data column).

    CREATE TABLE MyTable2    (Field1 VARCHAR(100) ,Field2 FLOAT ,  Field3 VARCHAR(100) , Field4 VARCHAR(100) ,    Field5 VARCHAR(100));
    GO
    
    INSERT  INTO dbo.MyTable2( Field1 , Field2 , Field3 , Field4 , Field5 )
    VALUES  ( 'Mango' , 50 ,'A,B,C,D,' ,'2,10,4,6' , '1,5,10,5' ), ( 'Apple' ,100 ,'H,K,L,N,' , '2,4,6,1' ,'1,5,3,2' );
    GO
    
    
    --select * from dbo.MyTable2
     
     
      
     select Field1, Field2, IDENTITY(int, 1,1) id
     , substring( Field3 ,  n, charindex(',', Field3  + ',',  n) -  n)  Field3
     
    into mytemp3
    from MyTable2
    cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n) 
    Where  n <= len(Field3) AND substring(',' + Field3,  n, 1) = ','
    
     
      select Field1, Field2, IDENTITY(int, 1,1) id
     , substring( Field4 ,  n, charindex(',', Field4  + ',',  n) -  n)  Field4
     
    into mytemp4
    from MyTable2
    cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n) 
    Where  n <= len(Field4) AND substring(',' + Field4,  n, 1) = ','
    
     select Field1, Field2, IDENTITY(int, 1,1) id
     , substring( Field5 ,  n, charindex(',', Field5  + ',',  n) -  n)  Field5 
    into mytemp5
    from MyTable2
    cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n) 
     
    Where  n <= len(Field5) AND substring(',' + Field5,  n, 1) = ','
     
     
     ;with mycte as (
     Select  m3.ID, m3.Field1, m3.Field2,  Field3, Field4, Field5 
     ,row_number() Over(Partition by m3.Field1 Order by m3.id ) rn 
     from mytemp3 m3 
     left join mytemp4 m4 on m3.Field1=m4.Field1 and m3.id=m4.id
     left join mytemp5 m5 on m3.Field1=m5.Field1 and m3.id=m5.id
     )
      
      
     Select Case when rn=1 then Field1 else  ''  end as Field1, Case when rn=1 then Field2 else  null  end as Field2, 
     Field3, Field4, Field5  
     from mycte
     
      --clean up
     drop table mytemp3, mytemp4, mytemp5 
    
    
    drop table dbo.MyTable2;

    Tuesday, November 8, 2016 8:00 PM
  • User-183374066 posted

    If it is just the matter of result set then you can do it like following

    CREATE TABLE MyTable2
        (
          Field1 VARCHAR(100) ,
          Field2 FLOAT ,
          Field3 VARCHAR(100) ,
          Field4 VARCHAR(100) ,
          Field5 VARCHAR(100)
        );
    GO
    
    INSERT  INTO dbo.MyTable2
            ( Field1 ,
              Field2 ,
              Field3 ,
              Field4 ,
              Field5
            )
    VALUES  ( 'Mango' ,
              50 ,
              'A,B,C,D,' ,
              '2,10,4,6' ,
              '1,5,10,5'
            );
    GO
    
    INSERT  INTO dbo.MyTable2
            ( Field1 ,
              Field2 ,
              Field3 ,
              Field4 ,
              Field5
            )
    VALUES  ( 'Apple' ,
              100 ,
              'H,K,L,N,' ,
              '2,4,6,1' ,
              '1,5,3,2'
            );
    GO
    
    
    
    CREATE FUNCTION dbo.Split
    (
        @Line nvarchar(MAX),
        @SplitOn nvarchar(5) = ','
    )
    RETURNS @RtnValue table
    (
        Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        Data nvarchar(100) NOT NULL
    )
    AS
    BEGIN
        IF @Line IS NULL RETURN
    
        DECLARE @split_on_len INT = LEN(@SplitOn)
        DECLARE @start_at INT = 1
        DECLARE @end_at INT
        DECLARE @data_len INT
    
        WHILE 1=1
        BEGIN
            SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at)
            SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END
            INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
            IF @end_at = 0 BREAK;
            SET @start_at = @end_at + @split_on_len
        END
    
        RETURN
    END

    and your query

    DECLARE @result TABLE
        (
          Field1 VARCHAR(100) ,
          Field2 VARCHAR(100) ,
          Field3 VARCHAR(100) ,
          Field4 VARCHAR(100) ,
          Field5 VARCHAR(100)
        );
    
    DECLARE @t TABLE
        (
          Id INT PRIMARY KEY
                 IDENTITY(1, 1) ,
          Field1 VARCHAR(100) ,
          Field2 FLOAT ,
          Field3 VARCHAR(100) ,
          Field4 VARCHAR(100) ,
          Field5 VARCHAR(100)
        );
    
    INSERT  INTO @t
            SELECT  *
            FROM    dbo.MyTable2;
    
    DECLARE @TotalRecords INT = ( SELECT    COUNT(*)
                                  FROM      @t
                                );
    
    DECLARE @row INT = 1;
    DECLARE @subRow INT = 1;
    DECLARE @t3 TABLE ( Id INT, Data VARCHAR(100) );
    DECLARE @t4 TABLE ( Id INT, Data VARCHAR(100) );
    DECLARE @t5 TABLE ( Id INT, Data VARCHAR(100) );
    DECLARE @Field1 VARCHAR(100); 
    DECLARE @Field2 FLOAT; 
    
    WHILE @row <= @TotalRecords
        BEGIN
            SET @subRow = 1;
            SELECT  @Field1 = Field1 ,
                    @Field2 = Field2
            FROM    @t
            WHERE   Id = @row;
    
            DELETE  FROM @t3;
            DELETE  FROM @t4;
            DELETE  FROM @t5;
    
            INSERT  INTO @t3
                    SELECT  *
                    FROM    dbo.Split(( SELECT  Field3
                                        FROM    @t
                                        WHERE   Id = @row
                                      ), ',')
                    WHERE   LEN(Data) > 0;
    
            INSERT  INTO @t4
                    SELECT  *
                    FROM    dbo.Split(( SELECT  Field4
                                        FROM    @t
                                        WHERE   Id = @row
                                      ), ',')
                    WHERE   LEN(Data) > 0;
    
            INSERT  INTO @t5
                    SELECT  *
                    FROM    dbo.Split(( SELECT  Field5
                                        FROM    @t
                                        WHERE   Id = @row
                                      ), ',')
                    WHERE   LEN(Data) > 0;
    
            WHILE @subRow <= ( SELECT   COUNT(*)
                               FROM     @t3
                             )
                BEGIN
    
                    IF ( @subRow = 1 )
                        BEGIN
                            INSERT  INTO @result
                                    ( Field1 ,
                                      Field2 ,
                                      Field3 ,
                                      Field4 ,
                                      Field5
                                    )
                            VALUES  ( @Field1 ,
                                      @Field2 ,
                                      ( SELECT  Data
                                        FROM    @t3
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t4
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t5
                                        WHERE   Id = @subRow
                                      )
                                    );
                        END;
                    ELSE
                        BEGIN
                            INSERT  INTO @result
                                    ( Field1 ,
                                      Field2 ,
                                      Field3 ,
                                      Field4 ,
                                      Field5
                                    )
                            VALUES  ( '' ,
                                      '' ,
                                      ( SELECT  Data
                                        FROM    @t3
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t4
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t5
                                        WHERE   Id = @subRow
                                      )
                                    );
                        END;
                
    
                    
    
                    SET @subRow += 1;
                END;
            SET @row += 1;
        END;
    
    SELECT  *
    FROM    @result;

    and your output

    By the way if you want to display data like that way than you can show hide things with previous set. Many things can be handle on the view and in c#

    Tuesday, November 8, 2016 8:03 PM
  • User2033107836 posted

    @Naseer

    Will you please tell me what you have added the extra code to remove duplicates? So that it clear for me to understand

    Tuesday, November 8, 2016 8:11 PM
  • User-183374066 posted

    Change Field2 from float to varchar

    DECLARE @result TABLE
        (
          Field1 VARCHAR(100) ,
          Field2 VARCHAR(100) ,
          Field3 VARCHAR(100) ,
          Field4 VARCHAR(100) ,
          Field5 VARCHAR(100)
        );

    and a check inside while which insert first two values only for the first time

     WHILE @subRow <= ( SELECT   COUNT(*)
                               FROM     @t3
                             )
                BEGIN
    
                    IF ( @subRow = 1 )
                        BEGIN
                            INSERT  INTO @result
                                    ( Field1 ,
                                      Field2 ,
                                      Field3 ,
                                      Field4 ,
                                      Field5
                                    )
                            VALUES  ( @Field1 ,
                                      @Field2 ,
                                      ( SELECT  Data
                                        FROM    @t3
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t4
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t5
                                        WHERE   Id = @subRow
                                      )
                                    );
                        END;
                    ELSE
                        BEGIN
                            INSERT  INTO @result
                                    ( Field1 ,
                                      Field2 ,
                                      Field3 ,
                                      Field4 ,
                                      Field5
                                    )
                            VALUES  ( '' ,
                                      '' ,
                                      ( SELECT  Data
                                        FROM    @t3
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t4
                                        WHERE   Id = @subRow
                                      ) ,
                                      ( SELECT  Data
                                        FROM    @t5
                                        WHERE   Id = @subRow
                                      )
                                    );
                        END;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 8, 2016 8:48 PM