Merging many rows into one.

Question

Merging many rows into one.

russcooper

SSC Journeyman

Points: 91
More actions
May 2, 2002 at 2:59 am

#158368

I need to "append" data in my rows together - clearer by example
I have data, obtained by joining 2 tables :
Name | LocationID | Location
--------------------------------
Russ | 1 | New York
Russ | 2 | London
I want to change the form to
Name | Location1 | Location
-----------------------------
Russ | New York | London
(I know there will be only be a max of 2 mappings )
can anyone help ?
Thanks

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Will LocationID always be 1 and 2 or other possiblities? If other then do you have a field that will uniquely ID Russ in you example?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Clive Strong SSChampion Points: 11222 More actions · Answer 2

Hey,

We have a similar issue. We have a table with 12 columns. We do have a unique rowid.

Basically we have the following (cut down) table

rowid

ordernumber

partnumber

linenumber

orderqty

shipqty

backorderqty

This is downloaded from an AS400 table.

This is basically a picking ticket so the only unique key is rowid.

In some cases (as we have now) is that there is one particular order and it has two lines. All columns match in the two lines except rowid and linenumber.

It doesn't matter which one of those rows is deleted, but one must go.

Any idea's??....friday evening and the brain is fizzled!

Thanks,

Clive Strong

clivestrong@btinternet.com

NPeeters SSChampion Points: 12503 More actions · Answer 3

A try for the first problem.

SELECT t.Name, t1.Location as Location1, t2.Location as Location2

FROM (Select name from table group by name) t

left outer join table t1 on t.name = t1.name AND t1.LocationID = 1

left outer join table t2 on t.name = t2.name

AND t2.LocationID = 2

This query does not require any of the records (with locationID 1 or 2) to be present. If you're certain that one of them always will be present, you can remove one of the outer joins.

NPeeters SSChampion Points: 12503 More actions · Answer 4

And now, let's have a go at the 'remove duplicates' problem from Clive.

DELETE FROM picktable WHERE rowid NOT IN

(SELECT max(p.rowid) FROM picktable p GROUP BY --(whatever identifies one order uniquely)--)

This is of course only in the case where rowid is a truly unique value in the table.

Clive Strong SSChampion Points: 11222 More actions · Answer 5

You the man!

That was so obvious when I looked at your code! Cheers!

Clive Strong

clivestrong@btinternet.com

lmayer Old Hand Points: 325 More actions · Answer 6

Hi there,

If I have the same situation multiple rows unique ID but the number of rows are dynamic would I have to create a join for each and how in the world would I know how many joins to do?

This is the code that returns multiple rows I want one comma delimited one that I can use for a mailmerge:

select p.people_code_id,

c.short_desc as Testt,t.medium_desc as TestType,t.alpha_score_1,t.alpha_score_2,t.alpha_score_3,convert (varchar,t.test_date,101)

from testscores t,people p, code_test c, code_testtype tt

where p.people_code_id = t.people_code_id

and c.code_value = t.test_id

and tt.code_value = t.test_type

Thanks in advance

Laura