SELECT T.EshipID, (SELECT TOP 1 Broker_Dealer FROM myTable WHERE Eship_ID = T.Eship_ID AND Status_FL = 'Available'),
(SELECT TOP 1 Broker_Dealer FROM myTable WHERE Eship_ID = T.Eship_ID AND Status_FL <> 'Available')
FROM myTable T

create table table1
(
	Eship_ID 	int,
	Broker_Dealer 	varchar(50),
	Status_FL	varchar(20)
)

insert into table1
select 134084, 'A.G. Edwards & Sons, Inc', 			'Available'	union all
select 134084, 'All', 						'Available'	union all
select 134084, 'Banc of America Investment Services Inc', 	'Not-Available'	union all
select 134084, 'Citicorp Investment Services', 			'Available'	union all
select 134084, 'Citigroup Global Markets, Inc.', 		'Available'
go

create function concatBroker
(
	@Eship_ID	int,
	@Status_FL	varchar(20)
)
RETURNS varchar(1000)
as
begin
	declare	@Broker_Dealer	varchar(1000)

	select	@Broker_Dealer = ''
	select	@Broker_Dealer = @Broker_Dealer + Broker_Dealer + ', '
		from	table1
		where	Eship_ID	= @Eship_ID
		and	Status_FL	= @Status_FL
		order by Broker_Dealer
	select	@Broker_Dealer= left(@Broker_Dealer, len(@Broker_Dealer) - 2)

	return @Broker_Dealer
end
go

select	Eship_ID, dbo.concatBroker(Eship_ID, 'Available'), dbo.concatBroker(Eship_ID, 'Not-Available')
from	table1
group by Eship_ID

go
drop function concatBroker
go
drop table table1

quote:

---

Originally posted by bharatsql

Hi Khtan,
I need something like that but the (insert) values are not static as they shall be changing. Also, based on the data provided above what I need is to concat the result set into two columns based on the Eshipid & Status_Fl. So that there should be a single row per eship_id.

Thanks

---