Data Split and Join with Table in TSQL

Question

0.00/5 (No votes)

See more:

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

SQL

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

HTML

-- 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

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

Posted 20-Mar-13 2:15am

gvprabu

Updated 20-Mar-13 2:23am

v3

Add a Solution

Comments

Kuthuparakkal 20-Mar-13 8:44am

Not clear enough, what's the issue here ?

ZurdoDev 20-Mar-13 8:58am

Logic for what? Does this not work? Be more clear please.

gvprabu 20-Mar-13 14:18pm

I am having data in @PCWDtls table. If Values is '1.1ü1.2' then I need to Insert 2 rows.
If '1.1ü1.2ü1.3' Then I need to Insert 3 rows. The Columns "CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM" Having same numbers of values like 1 or 2 or 3 ect... based on this I need to make rows.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Khandekar · Answer 1 · 2013-03-20T03:21:00

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[^]

SQL

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

Regards,

gvprabu · Answer 2 · 2013-03-20T16:49:00

I got solution using loop Logic...

SQL

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

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

Data Split and Join with Table in TSQL

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0