TSQL中的数据拆分和表连接 [英] Data Split and Join with Table in TSQL
本文介绍了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屋!
查看全文