CODEWITHSUNDEEP
sqlsql-server-2008
Savol
Savol

Reputation: 33

join multiple rows from one table into one row from another table

I have two tables that look like this:
First:

IdlokoStav,loko
12345,740 551-7

Second:

idPersonalStav, personal, idLokostav
23456, Some Dude, 12345
23457, Another Dude, 12345

Result should look like:

loko, Personal1, Personal2, personal3
740 551-7,Some Dude, Another Dude, NULL

There can be up to three people per one IdlokoStav, after I get this, I can join other things based on the result.
Db is Microsoft SQL 2008.

EDIT: I don´t care about how the people are ordered in the result as long as all three matches are included.

Upvotes: 1

Views: 615

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Normally, you don't want to put values into separate columns like this for further processing in SQL. One method is to use pivot; another, conditional aggregation. Both rely on the row_number() function:

select f.Idloko,
       max(case when seqnum = 1 then s.personal end) as personal1,
       max(case when seqnum = 2 then s.personal end) as personal2,
       max(case when seqnum = 3 then s.personal end) as personal3
from first f left join
     (select s.*,
             row_number() over (partition by IdlokoStav order by idPersonalStav) as seqnum
      from second s
     ) s
     on f.IdlokoStav = s.IdlokoStav
group by f.Idloko;

If you discover that you have more than three names, then you'll need to add more max() statements in the outer select.

Upvotes: 2

Related Questions