HELP NEEDED TO RETURN A SINGLE ROW FROM MULTIPLE RESULTS

Question

HELP NEEDED TO RETURN A SINGLE ROW FROM MULTIPLE RESULTS

SGT_squeequal

SSCertifiable

Points: 7167
More actions
January 13, 2010 at 3:40 am

#103879

Hello everyone, i have a new column in a table that i want to populate with all the areas of work that my staff are in
STAFF_IDInterested ManagerWORK, area
1 null 1009
1 null 8900
2 null 8876
3 null 1009
3 null 8900
3 null 2345
4 null 5679
the work area comes from another table and i want the staf id table to look like this
STAFF_IDinterested manager
1 1009,8900
2 8876
3 1009,8900,2345
4 5679
does anyone know how i can update this collumn by looping through and updating the interested manager column with the values from the work area table
???
***The first step is always the hardest *******

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I have no idea how to program it using a loop. But a set based solution could look like the following (and is usually a lot faster....)

SELECT

STAFF_ID,

stuff(( SELECT ', ' + area FROM table t2 WHERE t2.STAFF_ID= t1.STAFF_ID FOR XML path('')),1,2,'')

FROM

table t1

GROUP BY

STAFF_ID

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

Thanks Lutz,

i have worked out a method for doing this now,

Step 1 create table with all my results in it, 2 create update table

step 3 create cursor to insert into table 2 if the staf id does not exist and if the staf id does exist then update that row to set the area=area+','+@variable for new area

***The first step is always the hardest *******

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

glen.wass (1/13/2010)
Thanks Lutz,
i have worked out a method for doing this now,
Step 1 create table with all my results in it, 2 create update table
step 3 create cursor to insert into table 2 if the staf id does not exist and if the staf id does exist then update that row to set the area=area+','+@variable for new area

Try the query which Lutz posted too - you will quickly realise why he's not interested in a loop-based method: his set-based method will be much simpler and blindingly fast.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 4

Never used stuff before

whats the FOR XML bit used for? as i constantly get a syntax error saying incorrect syntax near xml

***The first step is always the hardest *******

LutzM SSC Guru Points: 107049 More actions · Answer 5

glen.wass (1/13/2010)
Never used stuff before
whats the FOR XML bit used for? as i constantly get a syntax error saying incorrect syntax near xml

What SQL Server version do you use?

Regarding the purpose of FOR XML: I tried to explain it in this post .

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 6

Hi Lutz,

I have tampered with the script you provided but cant get it to retrieve the results that i need.

I use SQL2000 and SQL2005 depending on what DB im working on.

cheers

Glen

***The first step is always the hardest *******

LutzM SSC Guru Points: 107049 More actions · Answer 7

Here's the solution I'd use as a complete test scenario. (Please note how I defined the sample data in a ready to use format). Also, this is a 2005 solution only. I don't have any 2000 version available, but IIRC the PATH operator is not valid in 2000.

declare @t TABLE (STAFF_ID INT, Interested_Manager_WORK varchar(50), area char(4))

INSERT INTO @t (STAFF_ID,area)

SELECT 1 ,'1009' UNION ALL

SELECT 1 ,'8900' UNION ALL

SELECT 2 ,'8876' UNION ALL

SELECT 3 ,'1009' UNION ALL

SELECT 3 ,'8900' UNION ALL

SELECT 3 ,'2345' UNION ALL

SELECT 4 ,'5679'

SELECT * FROM @t

;with cte as

(

SELECT

STAFF_ID,

stuff(( SELECT ', ' + area FROM @t t2 WHERE t2.STAFF_ID= t1.STAFF_ID FOR XML path('')),1,2,'') AS upd

FROM @t t1

GROUP BY STAFF_ID

)

UPDATE t

SET t.Interested_Manager_WORK = cte.upd

FROM @t t INNER JOIN cte ON t.STAFF_ID= cte.STAFF_ID

SELECT * FROM @t

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 8

Lutz, sorry for delayed response, yes tried your script and fantastic thanks wokes slightly different to mine but wow the performace is very good using the stuff thank you....:-D

***The first step is always the hardest *******