ACCTID            Industry
123                 Auto
123                 Bike
333                 Train
444                 Bike
555                 Auto
555                 Bike
555                 Train


What I am trying to get is such

AcctID     Industry_1      Industry_2        Industry_3
123           Auto           Bike              null
333           Train          null              null
444           Bike           null              null
555           Auto           Bike              Train

--this represents your table
declare @t table (acctid int, industry varchar(10))
insert @t(ACCTID ,           Industry)
select 123,                 'Auto' union all
select 123,                 'Bike' union all
select 333,                 'Train' union all
select 444,                 'Bike' union all
select 555,                 'Auto' union all
select 555,                 'Bike' union all
select 555,                 'Train'

--create a helper #tempp table
create table #just3 (acctid int, industry varchar(10), rowid int, primary key clustered (acctid, industry))

--populate the helper table (with just 3 industries for each acctid)
insert #just3 (acctid, industry)
select acctid, industry 
from @t t 
where industry in (select top 3 industry from @t where acctid = t.acctid order by industry)
order by acctid, industry

--populate our rowid column 
declare @r int, @a varchar(10)
select @a = '', @r = 1

update #just3 set
	@r = rowid = case when acctid = @a then @r + 1 else 1 end
	,@a = acctid

--final select statement
select	acctid
	,industry1 = max(case when rowid = 1 then industry end)
	,industry2 = max(case when rowid = 2 then industry end)
	,industry3 = max(case when rowid = 3 then industry end)
from	#just3
group by acctid

drop table #just3

output:
acctid      industry1  industry2  industry3  
----------- ---------- ---------- ---------- 
123         Auto       Bike       NULL
333         Train      NULL       NULL
444         Bike       NULL       NULL
555         Auto       Bike       Train

quote:

---

Originally posted by chrispy

TG,

Thanks!! Not exactly the solution but more then enough ideas and examples that I got what I needed from it and was able to make it work.

Thanks again,
Chris

---