TSQL中的数据拆分和表连接 [英] Data Split and Join with Table in TSQL

问题描述

嗨朋友们，

我需要解决以下问题，我的数据如下...

Hi Friends,
I need to solve the following Problem, I am having Data as follows...

DECLARE @PCWDtls TABLE (CLAUSEPCODE VARCHAR(40), CLAUSECODE VARCHAR(20), CLAUSEPLAN VARCHAR(20), CLAUSERATE VARCHAR(40), 
CLAUSEPREM VARCHAR(40), CLAUSEINTNO VARCHAR(500), CLAUSEINTSEQ VARCHAR(500), CLAUSEINTSI VARCHAR(1000), CLAUSEINTPREM VARCHAR(1000))

INSERT INTO @PCWDtls(CLAUSEPCODE , CLAUSECODE , CLAUSEPLAN , CLAUSERATE , CLAUSEPREM , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM)
SELECT 'FC819*FIRE','FC819','FIRE','25000000','-4350','1.2','1.2','20000000','17400'
UNION ALL
SELECT 'FC825(ii)*FIRE','FC825(ii)','FIRE','25000','2500','1.1','1.1','10000000','8700'
UNION ALL
SELECT 'FC825b(ii)*FIRE','FC825b(ii)','FIRE','2500','250','1.1','1.1','10000000','8700'
UNION ALL
SELECT 'FC842A*FIRE','FC842A','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
UNION ALL 
SELECT 'FC843*FIRE','FC843','FIRE','5000','1000','1.2','1.2','20000000','17400'
UNION ALL
SELECT 'FC845*FIRE','FC845','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
UNION ALL
SELECT 'FC846.01*FIRE','FC846.01','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'

SELECT CLAUSEPCODE , CLAUSECODE , CLAUSEPLAN , CLAUSERATE , CLAUSEPREM , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM 
FROM @PCWDtls P

-- Required Output
CLAUSEPCODE   CLAUSECODE CLAUSEPLAN CLAUSERATE CLAUSEPREM CLAUSEINTNO CLAUSEINTSEQ CLAUSEINTSI 
FC819*FIRE	FC819	  FIRE	    25000000	-4350	  1.2	      1.2	   20000000
FC825(ii)*FIRE	FC825(ii) FIRE	    25000	2500	  1.1	      1.1	   10000000
FC825b(ii)*FIRE	FC825b(ii)FIRE	    2500	250	  1.1	      1.1	   10000000
FC842A*FIRE	FC842A	  FIRE	    0	        0	  1.1	      1.1	   10000000
FC842A*FIRE	FC842A	  FIRE	    0	        0	  1.2	      1.2	   20000000
FC843*FIRE	FC843	  FIRE	    5000	1000	  1.2	      1.2	   20000000
FC845*FIRE	FC845	  FIRE 	    0	        0	  1.1	      1.1	   10000000
FC845*FIRE	FC845	  FIRE	    0	        0	  1.2	      1.2	   20000000
FC846.01*FIRE	FC846.01  FIRE	    0	        0	  1.1	      1.1	   10000000
FC846.01*FIRE	FC846.01  FIRE	    0	        0	  1.2	      1.2	   20000000

请给我没有临时表和循环逻辑的解决方案......

如果临时表也没问题，但我需要在SELECT语句中使用一些逻辑

在此先感谢



问候，

GVPrabu

Please give me the Solution with out Temp table and Loop logic....
If Temp table also fine but I need some Logic in SELECT Statement
Thanks in Advance

Regards,
GVPrabu

推荐答案

你好，



您可以使用Transact-SQL行构造函数（也称为表值构造函数）在单个INSERT语句中指定多个行。行构造函数由单个VALUES子句组成，其中多个值列表括在括号中并用逗号分隔。有关这方面的更多信息，请访问MSDN [ ^ ]

Hello,

You can use the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. More information on this can be found on MSDN[^]

INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');

问候，

Regards,

我得到了解决方案使用循环逻辑...

I got solution using loop Logic...

DECLARE @PCWDtls TABLE (CLAUSEID INT IDENTITY(1,1),CLAUSEPCODE VARCHAR(40), CLAUSECODE VARCHAR(20), CLAUSEPLAN VARCHAR(20), CLAUSERATE VARCHAR(40), 
CLAUSEPREM VARCHAR(40), CLAUSEINTNO VARCHAR(500), CLAUSEINTSEQ VARCHAR(500), CLAUSEINTSI VARCHAR(1000), CLAUSEINTPREM VARCHAR(1000))
DECLARE @PCWFinal TABLE(CLAUSECODE VARCHAR(20), CLAUSEINTNO VARCHAR(500), CLAUSEINTSEQ VARCHAR(500), CLAUSEINTSI VARCHAR(1000), CLAUSEINTPREM VARCHAR(1000), RECCOUNT INT)

INSERT INTO @PCWDtls(CLAUSEPCODE , CLAUSECODE , CLAUSEPLAN , CLAUSERATE , CLAUSEPREM , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM)
SELECT 'FC819*FIRE','FC819','FIRE','25000000','-4350','1.2','1.2','20000000','17400'
UNION ALL
SELECT 'FC825(ii)*FIRE','FC825(ii)','FIRE','25000','2500','1.1','1.1','10000000','8700'
UNION ALL
SELECT 'FC825b(ii)*FIRE','FC825b(ii)','FIRE','2500','250','1.1','1.1','10000000','8700'
UNION ALL
SELECT 'FC842A*FIRE','FC842A','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
UNION ALL 
SELECT 'FC843*FIRE','FC843','FIRE','5000','1000','1.2','1.2','20000000','17400'
UNION ALL
SELECT 'FC845*FIRE','FC845','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
UNION ALL
SELECT 'FC846.01*FIRE','FC846.01','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
-- Given Input
SELECT CLAUSECODE , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM 
FROM @PCWDtls 

DECLARE @RecCount INT, @I INT=1,
	@CLAUSECODE VARCHAR(40), @CLAUSEINTNO VARCHAR(500), @CLAUSEINTSEQ VARCHAR(500), @CLAUSEINTSI VARCHAR(1000), @CLAUSEINTPREM VARCHAR(1000)
SELECT @RecCount = COUNT(CLAUSEID) FROM @PCWDtls 
WHILE @I<=@RecCount 
BEGIN
	SELECT @CLAUSECODE=CLAUSECODE, @CLAUSEINTNO=CLAUSEINTNO, @CLAUSEINTSEQ=CLAUSEINTSEQ, @CLAUSEINTSI=CLAUSEINTSI, @CLAUSEINTPREM=CLAUSEINTPREM FROM @PCWDtls WHERE CLAUSEID=@I
	IF CHARINDEX('ü',@CLAUSEINTNO,1)=0 -- Single Values
	BEGIN
		INSERT INTO @PCWFinal(RECCOUNT, CLAUSECODE , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM)
		SELECT 1, @CLAUSECODE , @CLAUSEINTNO , @CLAUSEINTSEQ , @CLAUSEINTSI , @CLAUSEINTPREM 
	END 
	ELSE 
	BEGIN
		-- Insert First Record
		INSERT INTO @PCWFinal(RECCOUNT, CLAUSECODE , CLAUSEINTNO )
		SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE,Item FROM dbo.fnSplitString(@CLAUSEINTNO,'ü')  
		-- Update Other Values
		UPDATE F SET F.CLAUSEINTSEQ=SEQ.Item, F.CLAUSEINTSI=SI.Item, F.CLAUSEINTPREM=PREM.Item 
		FROM @PCWFinal F
		INNER JOIN (SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE 'CLAUSECODE',Item FROM dbo.fnSplitString(@CLAUSEINTSEQ,'ü')) SEQ ON SEQ.CLAUSECODE=F.CLAUSECODE AND SEQ.RECCOUNT=F.RECCOUNT
		INNER JOIN (SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE 'CLAUSECODE',Item FROM dbo.fnSplitString(@CLAUSEINTSI,'ü')) SI ON SI.CLAUSECODE=F.CLAUSECODE AND SI.RECCOUNT=F.RECCOUNT
		INNER JOIN (SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE 'CLAUSECODE',Item FROM dbo.fnSplitString(@CLAUSEINTPREM,'ü')) PREM ON PREM.CLAUSECODE=F.CLAUSECODE AND PREM.RECCOUNT=F.RECCOUNT
		
	END
	SELECT @I=@I+1
END
-- Required Output
SELECT D.CLAUSEPCODE, D.CLAUSECODE, D.CLAUSEPLAN, D.CLAUSERATE, D.CLAUSEPREM, F.CLAUSEINTNO, F.CLAUSEINTSEQ, F.CLAUSEINTSI, F.CLAUSEINTPREM 
FROM @PCWFinal F
INNER JOIN @PCWDtls D ON D.CLAUSECODE=F.CLAUSECODE  

其他比这有任何解决方案，请告诉我....

问候，

GVPrabu

Other than this any solutions are there, Please let me know....
Regards,
GVPrabu

这篇关于TSQL中的数据拆分和表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！