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

Loop Question

Sorry if this is a bit of a newbie question.

I have three tables.

DECLARE @.TempLineStatusXref TABLE(
Run_Id int,
Line varchar(50),
master_unit_id int,
Start_Time datetime,
End_Time datetime,
Duration numeric(18,9),
Team varchar(50),
Excluded int
)

CREATE TABLE dbo.#TempVars(
Var_Id INTEGER,
Data_Type_Id INTEGER,
User_Defined1 VARCHAR(10),
PU_Order INTEGER,
PU_Id INTEGER,
RequiredinReport INTEGER)

DECLARE @.Required TABLE (
Var_id int,
Run_id int
)

In Table @.TempLineStatusXref table I have 8 records... 2 of them have a value of Excluded = 1... the other 6 have a value of Excluded = 0.

In Table #TempVars I have 334 values all of which are required.

What I want to do is for Each record in @.TempLineStatusXref that has a 0 in the Excluded table add the field Var_id from #TempVars and the Run_id from @.TempLineStatusXref.

So I should end up with (334 * 6) = 2004 records in @.Required

I appreciate any help.

Mark

Mark:

Try one of these two alternatives:

insert into @.required
select a.var_id,
b.run_id
from #tempVars a
inner join @.TempLineStatusXref b
on b.excluded = 0

or

insert into @.required
select a.var_id,
b.run_id
from #tempVars a
cross join @.TempLineStatusXref b
where b.excluded = 0

|||

yeap..

Insert into @.Required
Select Var_Id, Run_Id
From @.TempLineStatusXref,#TempVars
where Excluded = 0

it worked... thanks!!!!

Looking for Query

SQL SERVER 2000
I have to tables
Students:
StudentSysID (INT) PK
StudentName (VCHAR 100)
NumTeachers (INT)
Classes:
SysID (INT) PK
StudentSysID (INT)
TeacherID (INT)
I am looking for a query to do the following
UPDATE Students SET NumTeachers = (SELECT DISTINCT COUNT(TeacherID) FROM
Classes WHERE Students.StudentSysID = Classes.StudentSysID)
Which would SEEM give me the number of DIFFERENT teachers each student has,
but its not, it gives me the total number of class records for each student.
What am i doing wrong?
TIA
Tim MorrisonDISTINCT should be inside the COUNT. Try if this works.
UPDATE Students SET NumTeachers = (SELECT COUNT(DISTINCT TeacherID)
FROM Classes WHERE Students.StudentSysID = Classes.StudentSysID)|||Without table structures & sample data for a repro script is hard to
identify the problem. Pl. refer to www.aspfaq.com/5006 to provide required
information for others to better identify the issue.
Anith|||I thought it would be something simple....
Much appreciated.
Tim Morrison
"Green" <subhash.daga@.gmail.com> wrote in message
news:1137098333.822885.223540@.f14g2000cwb.googlegroups.com...
> DISTINCT should be inside the COUNT. Try if this works.
> UPDATE Students SET NumTeachers = (SELECT COUNT(DISTINCT TeacherID)
> FROM Classes WHERE Students.StudentSysID = Classes.StudentSysID)
>