Rader till kolumner

Hej! Trevligt forum detta med kunnigt folk. Jag har sökt på mitt problem och har hittat en tråd med nästan samma problem som mitt (http://www.webforum.nu/showthread.php?t=174965), men jag gör ett försök eftersom mitt upplägg inte är riktigt likadant.

I SQL Server 2005 har jag två tabeller med en struktur som kan exemplifieras enligt följande.

Kolumner Tabell 1
Bilägarens ID
Bilägarens namn

Kolumner Tabell 2
Bilägarens ID
Bilens märke

Tabell1 förhåller sig som en-till-många mot Tabell2

Nu till mitt problem. Går det att skriva en SQL-fråga som returnerar en rad för varje bilägare med bilägarens bilmärken i kolumnerna? Typ så här

Namn            Bil1          Bil2          Bil3          Bil4          Bil5
Olle Olsson     Volvo         Saab          Mitsubishi
Nisse Nilsson   Toyota
Pelle Persson   Saab          Skoda

Kruxet är alltså att vissa bilägare bara har en bil medan andra kan ha flera. Jag kan tänka mig att man begränsar sig till att säga att man bara vill veta upp till 5 st om det är till någon hjälp. De bilägare som inte har 5 bilar får då null-värden i dessa kolumner.

Det borde väl finnas något datum i tabell2!
Eller skall man bara sopa in max fem bilar per Id?

Välkommen till wF ;-)

Det här känns som något man löser i presentationen, inte i SQL.
Men det beror ju lite på syftet förstås.

mvh

En standard Left Join borde passa, sedan när du presenterar det så kan du formatera det som du vill.

Kolumner Tabell 1
Bilägarens ID
Bilägarens namn

Kolumner Tabell 2
Bilägarens ID
Bilens märke

Alltså, som snabel-anders säger så är det något du borde lösa i presentationen. Typ så här: (Detta är pseudo-kod, alltså låtsas-kod för att visa en princip, som inte är skriven i något speciellt språk. Detta eftersom jag har noll koll på ASP.)

resultset = SQL("SELECT Owner.name, Car.brand FROM Owner LEFT JOIN Car ON Owner.ID = Car.ownerID")

currentowner = ""

while(not eof(resultset)){
  row = getrow(resultset)
  
  if(row("name") <> currentowner){
    currentowner = row("name")
    print "<h1>" & currentowner & "</h1>"
  }

  if(row("brand") <> "null"){
    print "(no car)"
  }else{
    print row("brand") & " ";
  }

}

SQL-frågan returnerar data på följande format:

+----------+----------+
| name     | brand    |
+----------+----------+
| Pelle    | Volvo    |
| Pelle    | SAAB     |
| Pelle    | Fiat     |
| Kalle    | Volvo    |
| Caroline | NULL     |
+----------+----------+

I exemplet har alltså Pelle tre bilar, Kalle har en medan stackars Caroline inte har någon bil alls. Den efterföljande koden tolkar sedan data på det formatet.

Skulle du sedan absolut vilja ha detta på det format du efterfrågade så går det, men det är inte att rekommendera av olika anledningar.

Tack för er hjälp. Tyvärr är detta inget som kan lösas i gränssnittet. Som jag skrev så är det exempel. Det är principen jag försöker lösa. I verkligheten handlar det om ca 20 000 rader med patienter och forskningsvariabler. Varje patient kan ha ett variabelt antal diagnoskoder som lagras i en separat tabell enligt en-till-många principen. Det är dessutom så att en patient också har åtgärdskoder som fungerar på samma sätt. När jag ställer frågan vill jag få data för varje patient på en rad för att sedan importera alltihop i ett program för statistisk analys.

Jag hoppades bara att någon kunde något finurligt som inte jag kan.

Nått sånt här kanske?
Testkört i sqlserver 2008.

create table t1
(
	id int,
	namn varchar(20)
)

create table t2
(
	id int,
	marke varchar(20)
)

insert into t1 values (1, 'Olle Olsson')
insert into t1 values (2, 'Nisse Nilsson')
insert into t1 values (3, 'Pelle Persson')

insert into t2 values (1, 'Volvo')
insert into t2 values (1, 'Saab')
insert into t2 values (1, 'Mitsubishi')

insert into t2 values (2, 'Toyota')
insert into t2 values (3, 'Saab')
insert into t2 values (3, 'Skoda')

declare @count int;
select @count = max(c) from (select count(*) c from t2 group by id) x;

declare @select varchar(max);
declare @join varchar(max);
declare @c int;
declare @v varchar(10);
set @select = 't1.namn, d1.marke';
set @join = 'left outer join data d1 on t1.id = d1.id and d1.r = 1';
set @c = 2;
while @c <= @count
begin
	set @v = CAST(@c as varchar);
	set @select = @select + ',d' + @v+'.marke';
	set @join = @join + ' left outer join data d' + @v +' on t1.id = d' + @v+'.id and d'+@v+'.r = 1+ d' + CAST(@c -1 as varchar) + '.r';
	set @c = @c + 1;
end

declare @kod nvarchar(max)
set @kod = 'with data as ('
	+'select t.id, t.marke, dense_rank() over(partition by t.id order by t.marke) r from t2 t'
+')'
+'select ' + @select +' from t1 '+@join;

exec sp_executesql @kod;

Bra adde2, ser ut att vara en snygg lösning. Vill gärna testa den senare. tror att det finns många tillfällen när man löst vill knyta en till många.
Hoppas att Stratocaster markerar detta som lösning.

Härligt! Det ser definitivt ut att fungera. Jag har inte möjlighet att testa förrän på måndag tyvärr.

adde2 skrev:

Nått sånt här kanske?
Testkört i sqlserver 2008.

Ojojoj adde2! Jag är grymt imponerad. Det fungerade fint. Tyvärr är jag ganska mycket uppe på läktaren vad gäller koden så jag måste sätta mig ner och granska det här så jag förstår hur jag kan använda det i fortsättningen.

Stort tack!

Hälsningar, Stratocaster

Lösningen är ju egentligen ett fulhack, men den funkar... :-)