January 26, 2006 at 6:28 am
Is the following possible by SQL alone, i.e. no cursors, want to see if you can do it by SQL alone.
TABLE1
ID,FIELD1,FIELD2
TABLE2
TABLE1_ID,ID
the tables are joined by TABLE1.ID and TABLE2.TABLE1_ID
TABLE2 can have one or many records relating to a single record in TABLE1
what I would like is a sql statement that would contain:
TABLE1.ID,TABLE1.FIELD1,TABLE2.ID all on a single line where if there were many records in TABLE2 relating to TABLE1 they would be separated by a comma in the field.
This is all to stop duplicate lines displayed where the only repeating factor would be differing ID's from TABLE2...... hope this makes sense
January 26, 2006 at 7:46 am
If this is only to stop duplicates from being displayed, you might want to try the code below. Displaying a comma separated list is harder and performes worse... Let me know if you want the code...
-- Test data
declare @TABLE1 table(ID int, FIELD1 varchar(10), FIELD2 varchar(10))
declare @TABLE2 table(TABLE1_ID int, ID int)
insert @TABLE1 select 1, 'a', 'b'
insert @TABLE1 select 2, 'c', 'd'
insert @TABLE2 select 1, 1
insert @TABLE2 select 1, 2
insert @TABLE2 select 1, 3
insert @TABLE2 select 2, 1
-- Select
select t1.ID, t1.FIELD1, t1.FIELD2, min(t2.ID) as ID2
from @TABLE1 t1 inner join @TABLE2 t2 on t1.ID = t2.TABLE1_ID
group by t1.ID, t1.FIELD1, t1.FIELD2
January 26, 2006 at 8:27 am
the result of your test scipt is
ID FIELD1 FIELD2 ID2
1 a b 1
2 c d 1
: or something similar so that all values of table2 relating to table1 is on a single line
so what I would like is :
ID FIELD1 FIELD2 ID2
1 a b 1,2,3
2 c d 1
January 27, 2006 at 3:19 am
Then try the following trick (which uses a function):
-- Test data
create table TABLE1 (ID int, FIELD1 varchar(10), FIELD2 varchar(10))
go
create table TABLE2 (TABLE1_ID int, ID int)
go
create function listValues(@ID int)
returns varchar(8000)
as
begin
declare @STR varchar(8000)
select @STR = ''
select @STR = @STR + (case when @STR = '' then '' else ',' end) + cast(Id as varchar) from TABLE2 where TABLE1_ID = @ID order by Id
return @STR
end
go
insert TABLE1 select 1, 'a', 'b'
insert TABLE1 select 2, 'c', 'd'
insert TABLE2 select 1, 1
insert TABLE2 select 1, 2
insert TABLE2 select 1, 3
insert TABLE2 select 2, 1
-- Select
select t1.ID, t1.FIELD1, t1.FIELD2, dbo.listValues(t1.ID) as ID2
from TABLE1 t1 inner join TABLE2 t2 on t1.ID = t2.TABLE1_ID
group by t1.ID, t1.FIELD1, t1.FIELD2
drop function listValues
go
drop table TABLE1
go
drop table TABLE2
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply