Arranging Data
-
I have an existing table structure as follows:
id owner VIN
1 dave 8690
2 matt 8690
3 joe 7713
4 barb 3417
What I need to do is re-arrange the data so the results are like:
id owner1 owner2 VIN
1 dave matt 8690
2 joe 7713
3 barb 3417
I have created a temp table that has the new structure but I'm stuck at how to best transpose the data.
-
I haven't tested this but if may give you what you are looking for.
Select a.owner as owner1,
b.owner as owner2,
a.VIN
From YourTable a
Left Outer Join YourTable b
On a.VIN = b.VIn
Where a.id <> b.id
Edit: I think the design of your original table is better than trying to put owner1 and owner2 in the same row. What are you going to do if there is a third owner?
Edit 2: The more I look at it I think this works better:
Select a.owner as owner1,
b.owner as owner2,
a.VIN
From YourTable a
Left Outer Join YourTable b
On a.VIN = b.VIn
And a.id <> b.id
-
Hi,
See if this works
select id, owner1,owner2, vin from ta left join
(select min(id), min(owner) as Owner1 from ta group by vin having count(*) >1) as mi on ta.id=mi.id left join
(select max(id),max(owner) as owner2 from ta group by vin having count(*) >1) as ma on ta.id = ma.id
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
I have now tested my previous 'solution' and it needs a small modification. The following select gets you the correct data. You can use it in conjuction with an Insert statement to your new table. I'm assuming your new table has an identity column.
Select a.owner as owner1,
b.owner as owner2,
a.VIN
From YourTable a
Left Outer Join YourTable b
On a.VIN = b.VIN
And a.id <> b.id
Where a.id =
(Select min(id)
From YourTable
Where VIN = a.VIN)
if you append "Order By a.id" the data will be returned in the order shown in your example.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply