compare two column of a table

Question

compare two column of a table

nio

SSC Rookie

Points: 27
More actions
August 9, 2009 at 2:15 am

#132633

I have the following table with columns: code,description,code2,code3,code4
, and I want to find the value of column1(code) witch
exist in column2(description) and if exist many code in description
insert it in the column3(code2),column4(code3),column5(code4)
Example
code ----description---------------code2----code3-----code4
0001---- aaa 0002 0003 0004------0002------0003------0004
0002---- bbbb 0004---------------0004-------null------null
0003---- rrrrr 0005----------------0005-------null---------
0004---- nnnnn
0005-----vvvvv
Thanks in Advance

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I’m sorry, but I can’t understand your request. Pleas take the time to read the article in my signature. The article explains how to post a question in the best way. If you’ll do as the article says, more people will be able to understand your needs and offer you there help.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Ordinarily, I'd agree with getting the OP to present data in a more readily consumable format according to the link provided above. However, this is Nica's first post so I'm going to provide a solution (provided that I interpreted it correctly).

This is a not-so-simple "split" problem where any "field" that is all numeric in the description should be split out into the other "CodeX" columns. There a two problems I have with this... why would anyone continue the problem of denormalized data and what is the maximum number of codes that can appear in the description?

Nica, we need an answer to my last question above in order to work on this. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

Ok... for the record, I think the original table is a really bad design but that's not something I'll even make a suggestion on.

Here's a solution... it requires a Tally table. Find out what it is, how to build one, and how it can be used to replace While Loops by reading the article at the following URL. Trust me, it's worth the read and it's worth building one. 😉

http://www.sqlservercentral.com/articles/T-SQL/62867/

Once a Tally table is in place, then it's just a matter of slicing this problem up into logical steps using CTE's. As a side bar, please read the comments in the code AND the article in the link Adi pointed out because it will help you get better, fully tested answers to your questions in the future. If you don't, then you're likely going to be ignored by some of the big dogs that can really work a treat for you. Here's the whole bit of code including the stuff you should have included and the answer to the problem...

--===== Create a demo table... this is not part of the solution.

-- It would help us a lot if your future posts contained such a thing.

IF OBJECT_ID('TempDB..#Demo','U') IS NOT NULL

DROP TABLE #Demo

;

CREATE TABLE #Demo

(

code CHAR(4),

description VARCHAR(30),

code2 CHAR(4),

code3 CHAR(4),

code4 CHAR(4)

)

;

--===== Populate the table with data. Again, it would help

-- us a lot if future posts contained such a thing.

INSERT INTO #Demo

(code,description)

SELECT '0001','aaa 0002 0003 0004' UNION ALL

SELECT '0002','bbbb 0004' UNION ALL

SELECT '0003','rrrrr 0005' UNION ALL

SELECT '0004','nnnnn' UNION ALL

SELECT '0005','vvvvv'

;

--===== This is a cascaded solution that "peels just one potato at a time".

-- The names of each CTE explain the purpose.

WITH cteCleaned AS

(

SELECT code,

RIGHT(description,LEN(Description)-PATINDEX('%[0-9]%',description)+1) AS codes

FROM #Demo

WHERE PATINDEX('%[0-9]%',description) > 0

)

,

cteSequenced AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY c.code ORDER BY t.N)+1 AS Sequence,

c.code,

SUBSTRING(' '+c.codes,t.N+1,CHARINDEX(' ',c.codes+' ',t.N)-t.N) AS codex

FROM cteCleaned c

CROSS JOIN dbo.Tally t

WHERE t.N < LEN(c.codes)

AND SUBSTRING(' '+c.codes,t.N,1) = ' '

)

,

ctePivoted AS

(

SELECT s.code,

MAX(CASE WHEN s.Sequence = 2 THEN s.codex END) AS code2,

MAX(CASE WHEN s.Sequence = 3 THEN s.codex END) AS code3,

MAX(CASE WHEN s.Sequence = 4 THEN s.codex END) AS code4

FROM cteSequenced s

GROUP BY s.code

)

UPDATE d

SET code2 = p.code2,

code3 = p.code3,

code4 = p.code4

FROM #Demo d

INNER JOIN ctePivoted p

ON d.code = p.code

SELECT * FROM #Demo

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

Jeff Moden (8/9/2009)
Ordinarily, I'd agree with getting the OP to present data in a more readily consumable format according to the link provided above. However, this is Nica's first post so I'm going to provide a solution (provided that I interpreted it correctly).

I wasn’t trying to punish the OP for the way that he presented his question. I really wanted to help him, but really didn’t understand his needs. My offer to read the article in my signature to learn how to present the question in a better way, was meant to help him and not to offend him in any way.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Adi Cohn (8/9/2009)
Jeff Moden (8/9/2009)
Ordinarily, I'd agree with getting the OP to present data in a more readily consumable format according to the link provided above. However, this is Nica's first post so I'm going to provide a solution (provided that I interpreted it correctly).
I wasn’t trying to punish the OP for the way that he presented his question. I really wanted to help him, but really didn’t understand his needs. My offer to read the article in my signature to learn how to present the question in a better way, was meant to help him and not to offend him in any way.
Adi

No, no... you were OK Adi... not a problem. I was just explaining why I was going around your good suggestions. You're fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

Nica,

I almost forgot... my code assumes that the values that are in the CODE column are unique. If they are not, then we really need you to post data and a CREATE TABLE statement along with any keys or indicies that may be available according to that same link that Adi posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)