recordset problems

[MartDawg]

I have two tables, when I join the data comes back like so...

field1, field2, field3, type1
field1, field2, field3, type2
field1, field2, field3, type3


Is it possible to make the data come back like so

field1, field2, field3, type1
type2
type3

 

[amrita418]

This requirement would have been very simple in oracle (using the break functionality).

As far as I know there is no straight forward way to do this in sql server.
Try something like this:

create table #TestBreak (Iden int identity(1, 1), Col1 varchar(10), Col2 varchar(10))

insert into #TestBreak values ('field1', 'Type1')
insert into #TestBreak values ('field1', 'Type1')
insert into #TestBreak values ('field2', 'Type1')
insert into #TestBreak values ('field2', 'Type1')
insert into #TestBreak values ('field2', 'Type1')

select 	  case when a.iden = b.iden then a.col1
             else '' end col1,
             a.col2 col2
from 	   #TestBreak a,
            (select 	col1,
                        col2,
                        min(iden) 'iden'
             from       #TestBreak 
             group by   col1, col2) b
where 	   a.col1 = b.col1
           and a.col2 = b.col2

As you can see I added an identity column and based my case stmt on it.

Hope this helps.
Regards,
AA