locked
Need Output Based On My Query RRS feed

  • Question

  • User2033107836 posted

    Hello

    This is my Table1 Data

    Field1 (varchar) Field2 (float) Field3 (varchar) Field4 (varchar) Field5 (varchar)
    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

    I want the result set to be below 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

    Need Sql Query

    Thank You

    Tuesday, November 8, 2016 2:34 PM

Answers

  • User-183374066 posted

    Hi asp.ambur,

    As you already gone through a lot of discussion regarding the data. I don't want to advice you more. I done it for you 

    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 to fetch result set

    DECLARE @result TABLE
        (
          Field1 VARCHAR(100) ,
          Field2 FLOAT ,
          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
    
    
    
                    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
                              )
                            );
    
                    SET @subRow += 1;
                END;
            SET @row += 1;
        END;
    
    SELECT  *
    FROM    @result;

    Not a recommended solution with large data but it is good as far as you select 10/20 records even from large data

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

All replies

  • User2053451246 posted

    Some consumable data to help answerers who choose to work on this:

    WITH cte AS (
    	SELECT
    		'Mango' AS Field1
    		, CONVERT(FLOAT, 50) AS Field2
    		, 'A,B,C,D' AS Field3
    		, '2,10,4,6' AS Field4
    		, '1,5,10,5' AS Field5
    
    	UNION ALL
    
    	SELECT
    		'Apple' AS Field1
    		, CONVERT(FLOAT, 100) AS Field2
    		, 'H,K,L,N' AS Field3
    		, '2,4,6,1' AS Field4
    		, '1,5,3,2' AS Field5
    )
    
    SELECT * FROM cte AS t

    PS: Not repeating Field1 and Field2 in the result set is usually handled by the UI, not the database.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Tuesday, November 8, 2016 2:46 PM
  • User2033107836 posted

    Some consumable data to help answerers who choose to work on this:

    WITH cte AS (
    	SELECT
    		'Mango' AS Field1
    		, CONVERT(FLOAT, 50) AS Field2
    		, 'A,B,C,D' AS Field3
    		, '2,10,4,6' AS Field4
    		, '1,5,10,5' AS Field5
    
    	UNION ALL
    
    	SELECT
    		'Apple' AS Field1
    		, CONVERT(FLOAT, 100) AS Field2
    		, 'H,K,L,N' AS Field3
    		, '2,4,6,1' AS Field4
    		, '1,5,3,2' AS Field5
    )
    
    SELECT * FROM cte AS t

    PS: Not repeating Field1 and Field2 in the result set is usually handled by the UI, not the database.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    I want the result set to be below 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

    Need Sql Query

    Tuesday, November 8, 2016 2:55 PM
  • User2053451246 posted

    ryanbesko

    Some consumable data to help answerers who choose to work on this:

    WITH cte AS (
    	SELECT
    		'Mango' AS Field1
    		, CONVERT(FLOAT, 50) AS Field2
    		, 'A,B,C,D' AS Field3
    		, '2,10,4,6' AS Field4
    		, '1,5,10,5' AS Field5
    
    	UNION ALL
    
    	SELECT
    		'Apple' AS Field1
    		, CONVERT(FLOAT, 100) AS Field2
    		, 'H,K,L,N' AS Field3
    		, '2,4,6,1' AS Field4
    		, '1,5,3,2' AS Field5
    )
    
    SELECT * FROM cte AS t

    PS: Not repeating Field1 and Field2 in the result set is usually handled by the UI, not the database.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    I want the result set to be below 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

    Need Sql Query

    Yea, we get that is what you want as the result.  What I posted is the correct way to expect help from someone on a forum.  A user can paste that into SSMS and have some data to work with.  I put effort into providing that, which you should always do when asking for data help.

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

    asp.ambur

    ryanbesko

    Some consumable data to help answerers who choose to work on this:

    WITH cte AS (
    	SELECT
    		'Mango' AS Field1
    		, CONVERT(FLOAT, 50) AS Field2
    		, 'A,B,C,D' AS Field3
    		, '2,10,4,6' AS Field4
    		, '1,5,10,5' AS Field5
    
    	UNION ALL
    
    	SELECT
    		'Apple' AS Field1
    		, CONVERT(FLOAT, 100) AS Field2
    		, 'H,K,L,N' AS Field3
    		, '2,4,6,1' AS Field4
    		, '1,5,3,2' AS Field5
    )
    
    SELECT * FROM cte AS t

    PS: Not repeating Field1 and Field2 in the result set is usually handled by the UI, not the database.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    I want the result set to be below 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

    Need Sql Query

    Yea, we get that is what you want as the result.  What I posted is the correct way to expect help from someone on a forum.  A user can paste that into SSMS and have some data to work with.  I put effort into providing that, which you should always do when asking for data help.

    Acutally in Table it would be any data. I need query not based on particular data

    Tuesday, November 8, 2016 3:17 PM
  • User77042963 posted

    You need to redesign your current table to a normalized table and then it will be much easy to write a query.

    Another important thing is to how to ask question for help. You are expecting to provide table DDL and sample data in script and let people know what you are expecting from your sample. It will save everybody's time and you will get help quicker. Thanks.

    Tuesday, November 8, 2016 3:29 PM
  • User2033107836 posted

    You need to redesign your current table to a normalized table and then it will be much easy to write a query.

    Another important thing is to how to ask question for help. You are expecting to provide table DDL and sample data in script and let people know what you are expecting from your sample. It will save everybody's time and you will get help quicker. Thanks.

    can it be possible to achive from my existing table..? I dont want to change my table 

    If it's possible please do give me the code. From next on i will take care 

    Last i'm a fresher here so i have few doubts that why i posted the samples to get output.

    Hope i will get the sql query

    Thanking You

    Tuesday, November 8, 2016 3:36 PM
  • User2053451246 posted

    We can't provide help with what you have given us. You straight up said the data you provided isn't what the data is going to be like in the real application. If we take the time to write code from what you provided you are going to come back and say "there are times when Field3 has more than 4 letters" and "there are times when Field4 has less than 4 numbers".

    Take the time to follow my and limno's instructions and the link I provided that explains how to post stuff like this to make it easier for us to help.  We will help, but we need more from you first.

    Tuesday, November 8, 2016 5:26 PM
  • User2033107836 posted

    We can't provide help with what you have given us. You straight up said the data you provided isn't what the data is going to be like in the real application. If we take the time to write code from what you provided you are going to come back and say "there are times when Field3 has more than 4 letters" and "there are times when Field4 has less than 4 numbers".

    Take the time to follow my and limno's instructions and the link I provided that explains how to post stuff like this to make it easier for us to help.  We will help, but we need more from you first.

    Thank You

    I have never changed and com back and says again and again you can see my first question and its clearly mentioned from my table data what out put i required.

    Thanks for your time

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

    Hi asp.ambur,

    As you already gone through a lot of discussion regarding the data. I don't want to advice you more. I done it for you 

    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 to fetch result set

    DECLARE @result TABLE
        (
          Field1 VARCHAR(100) ,
          Field2 FLOAT ,
          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
    
    
    
                    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
                              )
                            );
    
                    SET @subRow += 1;
                END;
            SET @row += 1;
        END;
    
    SELECT  *
    FROM    @result;

    Not a recommended solution with large data but it is good as far as you select 10/20 records even from large data

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

    @thanks - 

    Nasser Malik 

    Thanks Lot

    Tuesday, November 8, 2016 6:55 PM
  • User77042963 posted

    Please check the post below and please post your question with DDL and sample data with scripts. Thanks.

    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.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 Field1, Field2 , Field3, Field4, Field5  
     from mycte
     
      --clean up
     drop table mytemp3, mytemp4, mytemp5 
    
    
    drop table dbo.MyTable2;

    Tuesday, November 8, 2016 7:00 PM