September 23, 2010 at 7:29 am
I have 2 tables.
Table 1:
Primary Key – ID
CreatedDateField
Table 2:
Table1’s Primary Key – records are listed multiple times
FromDate
City
Results can look like this:
Table 1
19/1/2010
29/5/2010
Table 2
19/12/2010Dublin
19/22/2010Sicily
29/15/2010Ontario
210/1/2010London
I want to get results from a query (without using a cursor) where I combine the tables in such a way that it looks like this:
19/1/2010 9/12/2010 Dublin 9/22/2010 Sicily
29/5/2010 9/15/2010 Ontario 10/1/2010 London
Do you know how to get this result?
September 23, 2010 at 11:07 am
Run this ...
declare @t1 table(a int, b datetime)
insert into @t1 values(1, '9/1/2010')
insert into @t1 values(2, '9/5/2010');
declare @t2 table(a int, b datetime, c varchar(10))
insert into @t2 values(1, '9/12/2010', 'Dublin')
insert into @t2 values(1, '9/22/2010', 'Sicily')
insert into @t2 values(2, '9/15/2010', 'Ontario')
insert into @t2 values(2, '10/1/2010', 'London')
select t1.a,convert(varchar(10),b,101) , (select convert(varchar(10),b,101)+' '+c as 'data()' from @t2 t2 where t2.a=t1.a for xml path('')) as st
from @t1 t1 order by a
-- result --
a st
1 09/01/2010 09/12/2010 Dublin 09/22/2010 Sicily
2 09/05/2010 09/15/2010 Ontario 10/01/2010 London
September 23, 2010 at 12:49 pm
The problem is that I will not actually know what the data is. The data I gave was just sample data to give an idea what it would look like.
September 23, 2010 at 1:04 pm
byrdmom2 (9/23/2010)
The problem is that I will not actually know what the data is. The data I gave was just sample data to give an idea what it would look like.
And it was in his solution only so he could test it.
You only need the last select statement
September 23, 2010 at 1:13 pm
YESSSSSSSS! 😀 I got it! I added select statements under the inserts as you said! This has been a pain in my neck! Thank you guys so much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply