locked
need help with SQL query RRS feed

  • Question

  • User351619809 posted

    I have the following table

    Table A
    ID   DocNumber  InternalDocnumber  date       DocType Descriptin     NameG                 NameGR          NumberPage
    1     12345      566767           12/23/2020   3      thisisaTest    test1, test2, test3    test6, test4    1
    2     23456      566768           12/23/2020   3      thisisaTest2   test4, test5, test6    test9, test4    2
    

    I have around 5000 rows in above table. I want to do the comma based split the nameG and NameGR and insert them in the same table or new table again. so for e.g

    ID   DocNumber  InternalDocnumber  date       DocType Description     NameG                 NameGR          NumberPage
    1     12345      566767           12/23/2020   3      thisisaTest    test1                 test6             1
    1     12345      566767           12/23/2020   3      thisisaTest    test2                 test4             1
    1     12345      566767           12/23/2020   3      thisisaTest    test3                 NULL              1
    2     23456      566768           12/23/2020   3      thisisaTest2   test4                 test9             2
    2     23456      566768           12/23/2020   3      thisisaTest2   test5                 test4             2
    2     23456      566768           12/23/2020   3      thisisaTest2   test6                  NULL             2
    



    I already got the split function . Here is what I tried to do, but it is not working

    NSERT INTO [dbo].[Table B]
                ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[nameGR]
               ,[NumberPage]
              
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    dbo.SplitString([NameG], ','),
    dbo.SplitString([NameGR], ','),
    NumberPage,
    
    from Table A
    GO

    Below is the split function that I got from internet:

    USE [db_recload]
    GO
    /****** Object:  UserDefinedFunction [dbo].[SplitString]    Script Date: 4/2/2020 6:54:08 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[SplitString]
    (    
          @Input NVARCHAR(MAX),
          @Character CHAR(1)
    )
    RETURNS @Output TABLE (
          Item NVARCHAR(1000)
    )
    AS
    BEGIN
          DECLARE @StartIndex INT, @EndIndex INT
     
          SET @StartIndex = 1
          IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
          BEGIN
                SET @Input = @Input + @Character
          END
     
          WHILE CHARINDEX(@Character, @Input) > 0
          BEGIN
                SET @EndIndex = CHARINDEX(@Character, @Input)
               
                INSERT INTO @Output(Item)
                SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
               
                SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
          END
     
          RETURN
    END
    

    Below is the create table script:

    CREATE TABLE [dbo].[TABLEA](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[DocNumber] [varchar](50) NULL,
    	[InternalDocNumber] [varchar](50) NULL,
    	[Date] [varchar](50) NULL,
    	[DocType] [varchar](50) NULL,
    	[Description] [varchar](50) NULL,
    	[NameG] [varchar](max) NULL,
    	[NameGR] [varchar](max) NULL,
    	[NumberPages] [varchar](50) NULL,
    	
     CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    
    

    Below is the insert script:

    INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (1
               ,1235
               ,'12/23/2020'
               ,3
               ,'this is a test'
               ,'test1, test2, test3'
               ,'test4,test5'
               ,1)
    
    GO
    
    USE [db_recload]
    GO
    
    INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (2
               ,3456
               ,'12/24/2020'
               ,3
               ,'this is a test1'
               ,'test4, test5, test6'
               ,'test7,test8'
               ,2)
    GO
    
    
    



    This is not returning the results like I want.  any help with this query will be highly appreciated.

    Friday, April 3, 2020 1:18 AM

Answers

  • User77042963 posted
    CREATE TABLE [dbo].[TABLEA](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[DocNumber] [varchar](50) NULL,
    	[InternalDocNumber] [varchar](50) NULL,
    	[Date] [varchar](50) NULL,
    	[DocType] [varchar](50) NULL,
    	[Description] [varchar](50) NULL,
    	[NameG] [varchar](max) NULL,
    	[NameGR] [varchar](max) NULL,
    	[NumberPages] [varchar](50) NULL)
    
    
    	INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (1
               ,1235
               ,'12/23/2020'
               ,3
               ,'this is a test'
               ,'test1, test2, test3'
               ,'test4,test5'
               ,1)
    
    GO
     
    
    INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (2
               ,3456
               ,'12/24/2020'
               ,3
               ,'this is a test1'
               ,'test4, test5, test6'
               ,'test7,test8'
               ,2)
    GO
    
    --select * from  [dbo].[TABLEA]
    
    
    ;with ctea as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    a.item [NameG]
    --,[NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item))  rn
    
    from TableA
    cross apply dbo.SplitString([NameG], ',') a
    )
    ,cteb as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    b.item [NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item)) rn
    
    from TableA
    cross apply dbo.SplitString([NameGR], ',') b
    )
    
    
    select a.DocNumber 
    ,a.InternalDocNumber 
    ,a.Date 
    ,a.DocType 
    ,a.Description
    ,a.NameG
    ,b.NameGR
    ,a.NumberPages
    
     from ctea a left join cteb b 
    on a.DocNumber=b.DocNumber and
    a.InternalDocNumber=b.InternalDocNumber
    and a.rn=b.rn
     
    drop TABLE [dbo].[TABLEA]
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2020 2:01 PM
  • User77042963 posted
    CREATE TABLE [dbo].[TABLEA](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[DocNumber] [varchar](50) NULL,
    	[InternalDocNumber] [varchar](50) NULL,
    	[Date] [varchar](50) NULL,
    	[DocType] [varchar](50) NULL,
    	[Description] [varchar](50) NULL,
    	[NameG] [varchar](max) NULL,
    	[NameGR] [varchar](max) NULL,
    	[NumberPages] [varchar](50) NULL)
    
    
    	INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (1
               ,1235
               ,'12/23/2020'
               ,3
               ,'this is a test'
               ,'test1, test2, test3'
               ,'test6, test4'
               ,1),
               (2
               ,3456
               ,'12/24/2020'
               ,3
               ,'this is a test1'
               ,'test4, test5, test6'
               ,'test9, test4'
               ,2)
    		   ,
    		   (6
               ,6789
               ,'12/24/2020'
               ,3
               ,'this is a test3'
               ,'test9'
               ,'test100, test15, test16'
               ,2)
    GO
    
    --select * from  [dbo].[TABLEA]
    
    
    ;with ctea as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    a.item [NameG]
    --,[NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item))  rn
    
    from TableA
    cross apply dbo.SplitString([NameG], ',') a
    )
    ,cteb as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    b.item [NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item) desc) rn
    
    from TableA
    cross apply dbo.SplitString([NameGR], ',') b
    )
    
    
    select coalesce(a.DocNumber,b.DocNumber)  DocNumber
    ,coalesce(a.InternalDocNumber ,b.InternalDocNumber ) InternalDocNumber
    ,coalesce(a.Date,b.Date) Date  
    ,coalesce(a.DocType,b.DocType) DocType
    ,coalesce(a.Description,b.Description) Description
    ,a.NameG
    ,b.NameGR
    ,coalesce(a.NumberPages,b.NumberPages)  NumberPages
     from ctea a full outer join cteb b 
    on a.DocNumber=b.DocNumber and
    a.InternalDocNumber=b.InternalDocNumber
    and a.rn=b.rn
     
    drop TABLE [dbo].[TABLEA]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2020 9:04 PM

All replies

  • User-719153870 posted

    Hi anjaliagarwal5,

    I already got the split function . Here is what I tried to do, but it is not working

    Can you describe what is "it is not working"? Do you receive any error message?

    In fact, the build-in STRING_SPLIT method shall meet your requirement but it requires Compatibility level 130 which you can update by T-SQL.

    However, there's a conflict in the output table you suggested, it shall automatically generate null value for NameGR which seems not so easy to implement, because there's actually no relationship between the field NameG and NameGR from the table A.

    By default, it shall returns 6 rather than 4 rows of data for NameG test1, test2, test3 & NameGR  test6, test4.

    For example,

    select Id,DocNumber,InternalDocNumber,Date,DocType,Description,NameG, value as [NameGR], NumberPages from (select Id,DocNumber,InternalDocNumber,Date,DocType,Description,value as [NameG],NameGR, NumberPages from TABLEA cross apply STRING_SPLIT(NameG, ','))a cross apply STRING_SPLIT(NameGR, ',')
    

    In this case, it will rerurn 12 rows of data.

    The reason why your function won't work, i guess it's because the number returned by the custom method does not match the number currently being queried.

    Any way, please provide the issue you met when using the funtion and let's see how it can be fixed.

    Best Regard,

    Yang Shen

    Friday, April 3, 2020 4:50 AM
  • User77042963 posted
    CREATE TABLE [dbo].[TABLEA](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[DocNumber] [varchar](50) NULL,
    	[InternalDocNumber] [varchar](50) NULL,
    	[Date] [varchar](50) NULL,
    	[DocType] [varchar](50) NULL,
    	[Description] [varchar](50) NULL,
    	[NameG] [varchar](max) NULL,
    	[NameGR] [varchar](max) NULL,
    	[NumberPages] [varchar](50) NULL)
    
    
    	INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (1
               ,1235
               ,'12/23/2020'
               ,3
               ,'this is a test'
               ,'test1, test2, test3'
               ,'test4,test5'
               ,1)
    
    GO
     
    
    INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (2
               ,3456
               ,'12/24/2020'
               ,3
               ,'this is a test1'
               ,'test4, test5, test6'
               ,'test7,test8'
               ,2)
    GO
    
    --select * from  [dbo].[TABLEA]
    
    
    ;with ctea as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    a.item [NameG]
    --,[NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item))  rn
    
    from TableA
    cross apply dbo.SplitString([NameG], ',') a
    )
    ,cteb as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    b.item [NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item)) rn
    
    from TableA
    cross apply dbo.SplitString([NameGR], ',') b
    )
    
    
    select a.DocNumber 
    ,a.InternalDocNumber 
    ,a.Date 
    ,a.DocType 
    ,a.Description
    ,a.NameG
    ,b.NameGR
    ,a.NumberPages
    
     from ctea a left join cteb b 
    on a.DocNumber=b.DocNumber and
    a.InternalDocNumber=b.InternalDocNumber
    and a.rn=b.rn
     
    drop TABLE [dbo].[TABLEA]
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2020 2:01 PM
  • User351619809 posted

    Thanks for the query. Your query is returning below results:

    DocNumber	InternalDocNumber	Date	   DocType	Description	  NameG	  NameGR	NumberPages
    1	         1235	              12/23/2020	3	this is a test	  test1	  test4	         1
    1	         1235	              12/23/2020	3	this is a test	  test2	  test5	         1
    1	         1235	              12/23/2020	3	this is a test	  test3	  NULL	         1
    2	         3456	             12/24/2020	        3	this is a test1	   test4  test7	         2
    2	         3456	             12/24/2020	        3	this is a test1	   test5  test8	         2
    2	         3456	             12/24/2020	        3	this is a test1	   test6  NULL	         2

    I want something like below:

    ID   DocNumber  InternalDocnumber  date       DocType Description     NameG                 NameGR          NumberPage
    1     12345      566767           12/23/2020   3      thisisaTest    test1                 test6             1
    1     12345      566767           12/23/2020   3      thisisaTest    test2                 test4             1
    1     12345      566767           12/23/2020   3      thisisaTest    test3                 NULL              1
    2     23456      566768           12/23/2020   3      thisisaTest2   test4                 test9             2
    2     23456      566768           12/23/2020   3      thisisaTest2   test5                 test4             2
    2     23456      566768           12/23/2020   3      thisisaTest2   test6                  NULL             2
    


    Basically,  the [nameGR] column should have Test6 in the first row and test4 in the second row instead of test4 in the first row and test5 in second row.

    Thank you again for all your help.

    Friday, April 3, 2020 8:06 PM
  • User77042963 posted

    Where can we get test6 or test9? They are not in your source table column at all.

    Friday, April 3, 2020 8:15 PM
  • User77042963 posted

    You need to double check your source data and your result.

    Thanks for posting your question with table DDL and insert scripts.

    CREATE TABLE [dbo].[TABLEA](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[DocNumber] [varchar](50) NULL,
    	[InternalDocNumber] [varchar](50) NULL,
    	[Date] [varchar](50) NULL,
    	[DocType] [varchar](50) NULL,
    	[Description] [varchar](50) NULL,
    	[NameG] [varchar](max) NULL,
    	[NameGR] [varchar](max) NULL,
    	[NumberPages] [varchar](50) NULL)
    
    
    	INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (1
               ,1235
               ,'12/23/2020'
               ,3
               ,'this is a test'
               ,'test1, test2, test3'
               ,'test6, test4'
               ,1),
               (2
               ,3456
               ,'12/24/2020'
               ,3
               ,'this is a test1'
               ,'test4, test5, test6'
               ,'test9, test4'
               ,2)
    GO
    
    --select * from  [dbo].[TABLEA]
    
    
    ;with ctea as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    a.item [NameG]
    --,[NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item))  rn
    
    from TableA
    cross apply dbo.SplitString([NameG], ',') a
    )
    ,cteb as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    b.item [NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item) desc) rn
    
    from TableA
    cross apply dbo.SplitString([NameGR], ',') b
    )
    
    
    select a.DocNumber 
    ,a.InternalDocNumber 
    ,a.Date 
    ,a.DocType 
    ,a.Description
    ,a.NameG
    ,b.NameGR
    ,a.NumberPages
    
     from ctea a left join cteb b 
    on a.DocNumber=b.DocNumber and
    a.InternalDocNumber=b.InternalDocNumber
    and a.rn=b.rn
     
    drop TABLE [dbo].[TABLEA]

    Friday, April 3, 2020 8:19 PM
  • User351619809 posted

    Hi,

    The query that you gave me in the morning is totally correct. Just one thing if I insert another row in the table

    If I insert this new row in the table:

    select * from tableA
    GO
    INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (2
               ,6789
               ,'12/24/2020'
               ,3
               ,'this is a test3'
               ,'test9'
               ,'test100, test15, test16'
               ,2)
    

    then the result for the newly  inserted row will be like this:

    DocNumber  InternalDocNumber  Date          docType    Description    NameG    NameGR   NumberPages
    6            6789             12/24/2020     3          thisistest3   test9    test100    2
    6            6789             12/24/2020     3          thisistest3   NULL     test15      2
    6            6789             12/24/2020     3          thisistest3   NULL     test16       2

    Its other way around too.

    Friday, April 3, 2020 8:54 PM
  • User77042963 posted
    CREATE TABLE [dbo].[TABLEA](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[DocNumber] [varchar](50) NULL,
    	[InternalDocNumber] [varchar](50) NULL,
    	[Date] [varchar](50) NULL,
    	[DocType] [varchar](50) NULL,
    	[Description] [varchar](50) NULL,
    	[NameG] [varchar](max) NULL,
    	[NameGR] [varchar](max) NULL,
    	[NumberPages] [varchar](50) NULL)
    
    
    	INSERT INTO [dbo].[TABLEA]
               ([DocNumber]
               ,[InternalDocNumber]
               ,[Date]
               ,[DocType]
               ,[Description]
               ,[NameG]
               ,[NameGR]
               ,[NumberPages])
         VALUES
               (1
               ,1235
               ,'12/23/2020'
               ,3
               ,'this is a test'
               ,'test1, test2, test3'
               ,'test6, test4'
               ,1),
               (2
               ,3456
               ,'12/24/2020'
               ,3
               ,'this is a test1'
               ,'test4, test5, test6'
               ,'test9, test4'
               ,2)
    		   ,
    		   (6
               ,6789
               ,'12/24/2020'
               ,3
               ,'this is a test3'
               ,'test9'
               ,'test100, test15, test16'
               ,2)
    GO
    
    --select * from  [dbo].[TABLEA]
    
    
    ;with ctea as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    a.item [NameG]
    --,[NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item))  rn
    
    from TableA
    cross apply dbo.SplitString([NameG], ',') a
    )
    ,cteb as (
    select
    DocNumber,
    InternalDocNumber,
    Date,
    DocType,
    Description,
    b.item [NameGR]
    ,
    NumberPages 
    ,row_number() Over(Partition by DocNumber,
    InternalDocNumber  Order by ltrim(item) desc) rn
    
    from TableA
    cross apply dbo.SplitString([NameGR], ',') b
    )
    
    
    select coalesce(a.DocNumber,b.DocNumber)  DocNumber
    ,coalesce(a.InternalDocNumber ,b.InternalDocNumber ) InternalDocNumber
    ,coalesce(a.Date,b.Date) Date  
    ,coalesce(a.DocType,b.DocType) DocType
    ,coalesce(a.Description,b.Description) Description
    ,a.NameG
    ,b.NameGR
    ,coalesce(a.NumberPages,b.NumberPages)  NumberPages
     from ctea a full outer join cteb b 
    on a.DocNumber=b.DocNumber and
    a.InternalDocNumber=b.InternalDocNumber
    and a.rn=b.rn
     
    drop TABLE [dbo].[TABLEA]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2020 9:04 PM
  • User351619809 posted

    With the above query results, I am getting:

    NameG     NameGR
    
    Test9   Test16

     It should be like my previous post says

    NameG     NameGR
    
    Test9   Test100

    I omitted other columns just for clarifying this. Although other columns are there.

    Friday, April 3, 2020 9:32 PM
  • User77042963 posted

    I am not going to continue for this but you should be able to figure out a way to sort to control which item should go first.

    Check your items to understand your final result and work on it.

    Friday, April 3, 2020 10:43 PM
  • User77042963 posted

    Another option is to modify the split function to include the sequence of split items. In some cases, that may be what you need based on the sequence.

    Friday, April 3, 2020 10:57 PM
  • User351619809 posted

    Ok. Thank you

    Saturday, April 4, 2020 12:47 AM