pivot table, crosstab

[surfah]

Hello, I'm new to SQL. Below is an example of what I am working with.

no vendor
2345 CPH
2410 NOE
2410 abcE
2419 CPH
2419 WWSP
2419 WWwP


I want to create a table like the following:
2345 CPH
2410 NOE, abcE
2419 CPH, WWSP, WWwP

I'm not sure how to put the loop within the query?? The script below only creates:
2419 CPH, WWSP, WWwP

Any solution, or better work around? Lead me to any good tutorials. Thank you in advance!

****************************************
create table #tmpvndlst(
vendid int identity(1,1),
opseq char(10),
vendor varchar(4))

INSERT INTO #tmpvndlst VALUES ('2345', 'CPH')
INSERT INTO #tmpvndlst VALUES ('2410', 'NOE')
INSERT INTO #tmpvndlst VALUES ('2410', 'abcE')
INSERT INTO #tmpvndlst VALUES ('2419', 'CPH')
INSERT INTO #tmpvndlst VALUES ('2419', 'WWSP')
INSERT INTO #tmpvndlst VALUES ('2419', '

http://WWwP')

select * from #tmpvndlst


declare @seq char(10)
declare @seq2 char(10)
declare @ln char(20)
declare @rows int
declare @cnt int
declare @fln char(20)

SELECT DISTINCT @seq2=opseq
from #tmpvndlst
group by opseq
HAVING COUNT(opseq) > 1

select @rows=count(opseq)
from #tmpvndlst
where opseq = @seq2

--print @rows

set @cnt = 1

set @ln = ''

while @cnt <= @rows

begin
select @seq=opseq, @ln=rtrim(@ln) + ', ' + a.vendor
from #tmpvndlst a
where a.opseq=@seq2

set @fln = rtrim(substring(@ln,3,len(@ln)))

set @cnt = @cnt + 1
set @ln = ''

end
print @seq + ' ' + @fln

drop table #tmpvndlst

 

[surfah]

--Below is my basic solution to my previous problem in case anyone wants to know..

create table #tmpvnd(
opseq varchar(10),
vendor varchar(4))

INSERT INTO #tmpvnd VALUES ('2345', 'CPH')
INSERT INTO #tmpvnd VALUES ('2410', 'NOE')
INSERT INTO #tmpvnd VALUES ('2410', 'abc')
INSERT INTO #tmpvnd VALUES ('2419', 'CPH')
INSERT INTO #tmpvnd VALUES ('2419', 'WWSP')
INSERT INTO #tmpvnd VALUES ('2419', 'WWP')

declare @opseq char(4)
declare @vendor char(4)

declare @rows int
declare @seq char(10)
declare @seq2 char(10)
declare @ln char(20)
declare @cnt int
declare @fln char(20)

declare cur_opvendor cursor for
select opseq
from #tmpvnd
order by opseq


open cur_opvendor
fetch next from cur_opvendor into
@opseq
while (@@fetch_status = 0)
begin
select @rows=count(opseq)
from #tmpvnd
where opseq = @opseq

set @cnt = 1

set @ln = ''

while @cnt <= @rows

begin
select @seq=opseq, @ln=rtrim(@ln) + ', ' + a.vendor
from #tmpvnd a
where a.opseq=@opseq

set @fln = rtrim(substring(@ln,3,len(@ln)))

set @cnt = @cnt + 1
set @ln = ''

end
print @seq + ' ' + @fln


fetch next from cur_opvendor into
@opseq
end

deallocate cur_opvendor


drop table #tmpvnd

 

[Glyndwr]

surfah,

a bit kak handed but works

drop table #tmpvndlst
drop table #withList
go
declare @list varchar(8000)

create table #tmpvndlst(
vendid int identity(1,1),
opseq char(10), 
vendor varchar(4))
create table #withlist(opseq char(10) , vendorList varchar(1000))
 
INSERT INTO #tmpvndlst VALUES ('2345', 'CPH')
INSERT INTO #tmpvndlst VALUES ('2410', 'NOE')
INSERT INTO #tmpvndlst VALUES ('2410', 'abcE')
INSERT INTO #tmpvndlst VALUES ('2419', 'CPH')
INSERT INTO #tmpvndlst VALUES ('2419', 'WWSP')
INSERT INTO #tmpvndlst VALUES ('2419', '[URL unfurl="true"]WWwP')[/URL]
 
DECLARE list_cursor CURSOR
READ_ONLY
FOR SELECT distinct opseq FROM  #tmpvndlst

DECLARE @name varchar(40)
OPEN list_cursor

FETCH NEXT FROM list_cursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
	set @list = null
	IF (@@fetch_status <> -2)
	BEGIN
		select @list =   case when @list is null then  vendor  
				      else @list +',' + vendor end	
		from #tmpvndlst
		where opseq = @name
	END
	insert into #withlist values( @name,@list)
	FETCH NEXT FROM list_cursor INTO @name
END

CLOSE list_cursor
DEALLOCATE list_cursor

select * from #withlist

Glyndwr