loop through temporary table in SQL

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Jigsaw:

Do you want the concatenated list ordered? If so, how?

|||

Jigsaw:

Here is a way to do this using a CTE:

declare @.mockup table
(ID INT NOT NULL ,PK INT , pocket VARCHAR(10))

insert into @.mockup
select 1, 1, 'p1' union all
select 2, 1, 'p2' union all
select 3, 2, 'p3' union all
select 4, 2, 'p4' union all
select 5, 3, 'p5' union all
select 6, 3, 'p6' union all
select 7, 4, 'p7' union all
select 8, 5, 'p1' union all
select 9, 5, 'p2' union all
select 10,5, 'p83'
--select * from @.mockup

declare @.output table (PK int, pocketList varchar(200))

;with pocketList as
( select row_number () over
( partition by PK
order by pocket
) as seq,
row_number () over
( partition by PK
order by pocket desc
) as revSeq,
PK,
ID,
pocket
from @.mockup
), recursivePockets as
( select seq,
revSeq,
PK,
pocket,
cast(pocket as varchar(max)) as [product]
from pocketList
where revSeq = 1
union all
select b.seq,
b.revSeq,
b.PK,
b.pocket,
cast(b.pocket + ', ' + product as varchar(max))
from recursivePockets a
inner join pocketList b
on b.revSeq = a.revSeq + 1
and b.pk = a.pk
)

insert into @.output
select pk,
product
from recursivePockets
where seq = 1
order by pk

-- pk product
-- -- -
-- 1 p1, p2
-- 2 p3, p4
-- 3 p5, p6
-- 4 p7
-- 5 p1, p2, p83

( Ooops! Almost forgout to load it into an output table; sorry )

|||Have a read of FOR XML

Example

SELECT ' '+rtrim(ltrim(pocket))
from mytable
where pk = 2
order by pocket
FOR XML PATH('')

This will return you 1 row with a space in between each item you want to concat