如何将两个拆分列插入两列表中 [英] How to insert two split columns into two columns of table

问题描述

我有一个函数，它将字符串和字符作为输入，它将按此字符分割字符串并返回列

i have a function which will take a string and a character as input and it will split string by this character and returns the column

DECLARE @strIndustryID varchar(MAX)='1,2,3,4,5,6,7,44,55,66,77,88'
DECLARE	@strIndustryDescription varchar(MAX)='desc1|desc2|desc3|desc4|desc5|desc6|desc7|desc44|desc55|desc66|desc77|desc88'
DECLARE @COUNT INT=0

SELECT * FROM dbo.Split(@strIndustryID,',');

SELECT * FROM dbo.Split(@strIndustryDescription,'|');

并按先查询输出

and output by first Query

1

2
3
4
5
6
7
44
55
66
77
88

和第二次查询

and from second query

desc1
desc2
desc3
desc4
desc5
desc6
desc7
desc44
desc55
desc66
desc77
desc88

现在我想输出像

now i want output like

<pre lang="vb">desc1
Col1 col2
2   desc2
3   desc3
4   desc4
5   desc5
.
.
.
.

我想插入这些table1中的列有一个名为col1和col2的coulm



我怎么能这样做？

and i want to insert these column into the table1 which has a coulm named col1 and col2

How can i Do this?

推荐答案

如下所示更改分割器功能

Hi,

Change your splitter function like below

CREATE FUNCTION [dbo].[Split]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
 
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
 
	Return
END

然后使用在sql

Then use below sql

select a.data,b.data from dbo.[Split](@strIndustryID,',') a, dbo.[Split](@strIndustryDescription,'|') b
where a.id = b.id

尝试如下。

Hi,

try like below.

DECLARE @strIndustryID varchar(MAX)='1,2,3,4,5,6,7,44,55,66,77,88'
DECLARE	@strIndustryDescription varchar(MAX)='desc1|desc2|desc3|desc4|desc5|desc6|desc7|desc44|desc55|desc66|desc77|desc88'
DECLARE @COUNT INT=0

CREATE TABLE #tbTemp1
{
    sl INT,
    value VARCHAR(100)
}

CREATE TABLE #tbTemp2
{
    sl INT,
    value VARCHAR(100)
} 

INSERT INTO #tbTemp1
SELECT ROW_NUMBER() OVER(ORDER BY col1 asc) 'sl', col1 FROM dbo.Split(@strIndustryID,',');

INSERT INTO #tbTemp2
SELECT ROW_NUMBER() OVER(ORDER BY col1 asc) 'sl', col2 FROM dbo.Split(@strIndustryDescription,'|');

SELECT A.value, B.value FROM #tbTemp1 as A INNER JOIN #tbTemp2 as B ON A.sl = B.sl

DROP TABLE #tbTemp1
DROP TABLE #tbTemp2

希望它有效。

hope it works.

首先更改您的分割功能，如下所示。它将返回Identity列以及值。

First of all change your split function as shown below. It will return Identity column as well as values.

CREATE FUNCTION  [dbo].[Split]
(  
	@RowData VARCHAR(MAX),  
	@SplitOn NVARCHAR(5)
)    
RETURNS @RtnValue TABLE
(  
	Id		INT IDENTITY(1,1),  
	Data	VARCHAR(MAX)  
)   
AS    
BEGIN   
	DECLARE @Cnt INT
	SET		@Cnt = 1  
  
	WHILE (CHARINDEX(@SplitOn,@RowData)>0)
	BEGIN
		INSERT INTO @RtnValue (data)
		SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData,1,CHARINDEX(@SplitOn,@RowData)-1)))  
  
		SET @RowData = SUBSTRING(@RowData,CHARINDEX(@SplitOn,@RowData)+1,LEN(@RowData))
		SET @Cnt = @Cnt + 1
	END  
   
	INSERT INTO @RtnValue (data)
	SELECT Data = LTRIM(RTRIM(@RowData))  
RETURN
END

执行以下块之后：

after that execute following block :

DECLARE @strIndustryID varchar(MAX)='1,2,3,4,5,6,7,44,55,66,77,88'
DECLARE	@strIndustryDescription varchar(MAX)='desc1|desc2|desc3|desc4|desc5|desc6|desc7|desc44|desc55|desc66|desc77|desc88'
DECLARE @COUNT INT=0

CREATE TABLE #TEMP1
(
	ID	INT,
	strVal VARCHAR(50)
)
INSERT	INTO #TEMP1
SELECT	* FROM dbo.Split(@strIndustryID,',');

CREATE TABLE #TEMP2
(
	ID	INT,
	strVal VARCHAR(50)
)
INSERT	INTO #TEMP2 
SELECT * FROM dbo.Split(@strIndustryDescription,'|');

INSERT  INTO Table1(COL1,COL2)
SELECT	T1.strVal,t2.strVal 
FROM	#TEMP1 T1
INNER JOIN #TEMP2 T2 ON T1.ID = T2.ID

DROP TABLE #TEMP2
DROP TABLE #TEMP1

这篇关于如何将两个拆分列插入两列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！