need help with SQL query

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.

Answer 1

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

Answer 2

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]

Answer 3

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.

Answer 4

User77042963 posted

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

Answer 5

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]

Answer 6

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.

Answer 7

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]

Answer 8

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.

Answer 9

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.

Answer 10

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.

Answer 11

User351619809 posted

Ok. Thank you