CODEWITHSUNDEEP
sqloracle-database
lauri
lauri

Reputation: 1

How to join 2 rows in one table by ID

I need to do statisitics about namesakes (people with same name and different personal_identidy_code). I would like to join 2 or more rows into 1 row so i can contioune comparing data with CASE statements.

Lets say, i have a query output like that:

acitivityNR  acitivityName  Personal_identity_code    Name
111             test             00000000001          hello wor
111             test2            00000000002          hello wor
111             test2            00000000002          hello wor
222             asd              11111111111          my name
222             asd2             11111111112          my name

There can be more then 2 rows with same activityNR (activityName can be different). I have more then 1 activitys in my output. I need to compare, if in activityNr 111 has people with same name, but different personal identity code. I have idea to join these rows into one, so i have output like that:

acitivityNR = aNR
acitivityName = aName
Personal_identity_code = code

aNR Aname Code         Name       Code2     Name2      Code3      Name3
111 test 00000000001 hello wor 00000000002 hello wor 00000000002 hello wor
222 asd  11111111111 my name   11111111112  my name

and from there i can use CASE statement to filter out outputs where code = code2 = code3

This is what i have, with some where sententences also, but thats not important right now.

select a.activityNR, a.activityName, p.prsonal_identity_code, p.name
from things t
inner join activitys a on t.id = a.things_id
inner join activitys_people ap on a.id = ap.activitys_id
inner join people p on ap.people_id = p.id
order by a.activityNR asc

Upvotes: 0

Views: 70

Answers (1)

Marlin Pierce
Marlin Pierce

Reputation: 10089

select a1.activityNR, a1.activityName, a1.prsonal_identity_code,
       a2.activityName, a2.prsonal_identity_code
from activitys a1
  inner join activitys a2 on a1.acitivityNR = a2.acitivityNR
where a1.prsonal_identity_code <> a2.prsonal_identity_code

This would give you results like the following:

aNR Aname Code         Name       Code2     Name2
111 test 00000000001 hello wor 00000000002 hello wor
111 test 00000000001 hello wor 00000000002 hello wor
222 asd  11111111111 my name   11111111112  my name

Upvotes: 1

Related Questions