update table with multiple rows from another table

[gatetec]

update intake
set intakecom = otherreason
where visitreason
from intakecom.clientno = visitreason.clientno

I have this update statement, but the issue I have is that tbl visitreason has multiple otherreason

i.e.

visitreason.clientno otherreason
1207 ALF
1207 OTS
1207 TAS
....

What I want is that intakecom has all of otherreason contents, so that it shows like below.

intakecom.clientno intakecom
1207 ALF, OTS,TAS

Please advise.

thx much

 

[RyanEK]

Hi,

Are you using SQL2005 and above? If so you could do the following:

select clientno,
replace((
     select otherreason as 'data()'
     from visitreason t2 
     where t1.clientno = t2.clientno
     for xml path('')),' ',',') as otherreason
into intakecom
from visitreason t1
group by clientno


[Code]


Ryan

 

[gatetec]

I am using SQL 2000

 

[drtree]

Hi,

You could use a cursor to loop through your source table and combine all of the rows (and then insert them into another table), but this might work too...

update intake
set intakecom = CASE WHEN intakecom IS NULL OR intakecomm = '' THEN otherreason ELSE intakecom + ', ' + otherreason END
from intakecom.clientno = visitreason.clientno 
where vistreason <> ''

Using the CASE WHEN... statement like that will allow you to embed the logic right within your Update statement and it should tie together the results like you're looking for...please note that this is untested.

HTH,

Doc Tree

 

[gatetec]

visitreason.clientno otherreason
564 Lack of Support, Emotional Problems
564 Lack of Support, Lack of Support
564 Lack of Support, Physical Impairment
564 Lack of Support, Poor Living Condition
564 Lack of Support, Poor Personal Hygien
.....

ok... The issue I am having is that there are duplicate contents. In this example, there are several 'Lack of Support'. How do you remove those so that it shows as below?

564 Lack of Support, Emotional Problems, Physical Impairment, Poor Living Condition, Poor Personal Hygien

thx so much

 

[drtree]

Hi,

Well, that certainly makes it more interesting. I guess you could modify the CASE in the prior example to be like this:

  CASE WHEN PATINDEX('%,%', OtherReason) = 0 THEN (CASE WHEN intakecom IS NULL OR intakecomm = '' THEN otherreason ELSE intakecom + ', ' + otherreason END)
       WHEN intakecom IS NULL OR intakecomm = '' THEN otherreason 
       WHEN PATINDEX('%' + LEFT(PATINDEX('%,%', OtherReason), PATINDEX('%,%', OtherReason) - 1) + '%', InTakeCom) = 0 THEN 
            InTakeCom + ', ' + LEFT(PATINDEX('%,%', OtherReason), PATINDEX('%,%', OtherReason) - 1 
       ELSE intakecom  END +
  CASE WHEN PATINDEX('%,%', OtherReason) = 0 OR Intakecom IS NULL OR intakecomm = '' THEN '' 
       WHEN PATINDEX('%' + LTRIM(Right(PATINDEX('%,%', OtherReason), LEN(OtherReason) - PATINDEX('%,%', OtherReason))) + '%', InTakeCom) = 0 THEN
            InTakeCom + LTRIM(Right(PATINDEX('%,%', OtherReason), LEN(OtherReason) - PATINDEX('%,%', OtherReason)))
       ELSE '' END

This is being done assuming that, at most, you'll be dealing with 2 items broken out by a comma. Essentially what I'm doing with this is using PATINDEX to find the comma, then breaking the string out into it's left/right most portions. Unfortunately, I don't have time to test this - so it may not work - but I wanted to give you an idea of how to proceed. If you end up encountering items where there's 3 items (i.e., Lack of Support, Physical Impairment, Poor Personal Hygiene), then I'd check out a cursor...

HTH,

Doc Tree

 

[gmmastros]

Read this:

thread183-1159740




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom