December 18, 2006 at 10:51 am
I have a table1
col1 col2 col3 col4 col5
A 1 2 3 4
one record
table2
col1 col2 col3
A 1 O
A 2 C
A 3 D
A 4 E
four records
now I want to join about only return the only one single row that looks like this
col1 col2 col3 col4 col5
A O C D E
I can be a correlated subquery but this is a ton of records and I am afraid it will run slow.
December 18, 2006 at 11:38 am
A couple of questions. Are you joining on col1 between the two tables? Assuming that is true, what are the maximum number of rows that a single col1 value has in table2?
If it is fixed, for example at 4 as in your data above, then it's pretty easy. If it's not fixed, then you're looking at dynamic SQL, and there are all sorts of "gotchas" that you have to be prepared for in that case.
December 19, 2006 at 11:00 am
This is adapted from a project I worked on a while ago using PIVOT.
create table correlate(
ID char(2) not null,
ColID1 int,
ColID2 int,
ColID3 int,
ColID4 int
)
create table vals(
ID char(2) not null,
ColID int not null,
ColValue char(5)
)
alter table vals
add constraint PK_Joint
primary key (ID,ColID)
-- Simple version
insert into correlate values ('A',1,2,3,4)
insert into vals values ('A',1,'F')
insert into vals values ('A',2,'R')
insert into vals values ('A',3,'E')
insert into vals values ('A',4,'D')
-- Handling a null
insert into correlate values ('B',1,null,3,2)
insert into vals values ('B',1,'A')
insert into vals values ('B',2,'M')
insert into vals values ('B',3,'J')
insert into vals values ('B',4,'B')
-- Miss some bits
insert into correlate values ('C',3,4,5,1)
insert into vals values ('C',1,'M')
insert into vals values ('C',2,'A')
insert into vals values ('C',3,'S')
insert into vals values ('C',4,'T')
insert into vals values ('C',5,'E')
insert into vals values ('C',6,'R')
select * from
(select vals.ID,vals.ColValue,
case ColID
when ColID1
then 'ColID1'
when ColID2
then 'ColID2'
when ColID3
then 'ColID3'
when ColID4
then 'ColID4'
else 'Ignore'
end as ColName
from vals,correlate
where vals.ID = correlate.ID) as D
PIVOT (Max(ColValue) For ColName IN ([ColID1],[ColID2],[ColID3],[ColID4])) as F
Fitz
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply