create table #moo (personname varchar(10))
create table #mooness (personname varchar(10), tran_date smalldatetime, transaction_amount int null)

insert into #moo values ('mr moo') -- One person
insert into #mooness values ('mr moo',getdate(),10)
insert into #mooness values ('mr moo',dateadd (dd, 1, getdate() ),20)

SELECT
 #moo.personname, firstone.tran_date as 'first tran date', firstone.value, secondone.tran_date as 'second tran date', secondone.value
FROM
	#moo
INNER JOIN
	(
	SELECT 
		#mooness.personname, tran_date, transaction_amount as value
	FROM
		#mooness
	INNER JOIN 
		(
			SELECT 
				personname, min (tran_date) as firstdate -- Get the first transaction only
			FROM
				#mooness
			group by personname
		) lowest
	on 
		lowest.firstdate = #mooness.tran_date
	) firstone
on #moo.personname = firstone.personname

INNER JOIN
	(
	SELECT 
		#mooness.personname, tran_date, transaction_amount as value
	FROM
		#mooness
	INNER JOIN 
		(
			SELECT 
				personname, max (tran_date) as lastdate
			FROM
				#mooness
			group by personname
		) highest
	on 
		highest.lastdate = #mooness.tran_date
	) secondone
on #moo.personname = secondone.personname


drop table #mooness
drop table #moo

quote:

---

Originally posted by alr37

Well thank you both (Jay & Jeff) for the well given criticism. Both your points are correct as far as formatting. The reason for doing all the formatting in the SQL statement is because I'm using test data and it was just easier at this point to do it there. But I will be handling the formatting on the presentation side.

I also know that a one-to-many cardinality will give me a result set of duplicate data because of the 'many' table. The data that I am returning is correct. Unfortunately for me, my employer wants to see the data all in one row. I thought that using derived tables for this was the solution. I'm creating a .Net windows application and am using component one’s TrueDBGrid. Component one’s Hierarchical dataview for their datagrid has serious problems so for right now I'm fighting the battle of getting everything on one line until Component One gets a patch out for their product.

So, is a Derived table the answer? Is it possible to do what I need to do? If so, can you please show me how?
Like I stated earlier I'm new to this and I'm trying to learn as I go. So if you kind people can lend a hand I'd be truly grateful. TIA



Thank You,
alr

---