update only one field in a table with concatenation

Question

Post reply

update only one field in a table with concatenation

Viewing 15 posts - 16 through 30 (of 30 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

David,

Now, be gentle with me... just now starting to get my feet wet on 2k5... 😉

...since the following two queries return the identical result set, why did you include "STUFF" and "GROUP BY" in your query? Is there an advantage for handling some odd form of data or ???

--===== David's query



 SELECT t1.ID,

	STUFF((',' + t1.Descr + (SELECT ',' + t2.Descr FROM @tbl2 t2 WHERE t1.id = t2.id FOR XML PATH(''))),1,1,'')

   FROM @tbl1 AS t1

  GROUP BY t1.id, t1.descr

--===== Jeff's query

 SELECT t1.ID,

	t1.Descr + (SELECT ',' + t2.Descr FROM @tbl2 t2 WHERE t1.id = t2.id FOR XML PATH(''))

FROM @tbl1 AS t1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

David McFarland SSChampion Points: 11815 More actions · Answer 2

Jeff Moden (12/18/2007)

David,

Now, be gentle with me... just now starting to get my feet wet on 2k5... 😉

...since the following two queries return the identical result set, why did you include "STUFF" and "GROUP BY" in your query? Is there an advantage for handling some odd form of data or ???

No advantage at all in this situation, and your's works just fine. I was just lazy and took our code and modified it just enough to fit the sample data. Unlike the OP, we use it without concatenating the second table's data to a column from the first table, and you get a prepended comma when you do that, so we use Stuff as a "clean" Substring(expression,2,999) replacement. Our data also sometimes has multiple rows in the first table that need to be rolled up, as our requirements don't always call for a unique identifier to be used as the row identifier, thus the group by. Basically, I took our code and applied it to a similar, but not identical problem. Our's is a fairly standard method these days, so I have no idea where the idea of using FOR XML in this manner originated (if I had to guess, it would be Itzik Ben-Gan, but that's really just a guess). It's sort of a hack, since you're really just creating a tagless XML string, but it sure works like a charm. To clarify, this modified example will show the reasoning behind the Stuff and Group By in my code.

declare @tbl1 table( id int, descr varchar(25))



declare @tbl2 table(	id int,	descr varchar(25))

insert into @tbl1

select 1, 'test1' union all

select 1, 'test1' union all

select 2, 'test2' 

insert into @tbl2

select 1, 'test1 additional' union all

select 1, 'test1 additional2' union all

select 1, 'test1 additional3' union all

select 2, 'test2 additional'

-- Stuff/Group by method

SELECT t1.ID,	Stuff((SELECT ',' + t2.Descr FROM @tbl2 t2 WHERE t1.id = t2.id FOR XML PATH('')),1,1,'')   FROM @tbl1 AS t1 GROUP BY t1.ID

-- Alternate method

SELECT t1.ID,	(SELECT ',' + t2.Descr FROM @tbl2 t2 WHERE t1.id = t2.id FOR XML PATH('')) FROM @tbl1 AS t1

Edit: I should note that when I refer to "row identifier" above, I mean in the results, not the table. The tables in our case have a primary key, but it's not always what they want the data grouped by. So ignore the fact that the example data I stole from Adam uses the name ID for the first column. If you're going to have one row of results per PK from the first table, you can definitely lose the Group By.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Thanks David... appreciate the explanation...

Yeah, I figured the Stuff and Group By out for the single table application... just didn't understand it for the two table application. Now, I do.

By the way, just to take this problem one small step further, if we have a parent entry with no children, this may be what you want to do instead of returning NULL...

DECLARE @tbl1 TABLE( ID INT, Descr VARCHAR(25))



DECLARE @tbl2 TABLE( ID INT, Descr VARCHAR(25))

 INSERT INTO @tbl1

 SELECT 1, 'test1' UNION ALL

 SELECT 2, 'test2' UNION ALL

 SELECT 3, 'test3'

INSERT INTO @tbl2

 SELECT 1, 'test1 additional'  UNION ALL

 SELECT 1, 'test1 additional2' UNION ALL

 SELECT 2, 'test2 additional'  

--===== Concatenate values in tbl2 and add them to values in table 1

 SELECT t1.ID,

		t1.Descr + ISNULL((SELECT ',' + t2.Descr FROM @tbl2 t2 WHERE t1.id = t2.id FOR XML PATH('')),'')

   FROM @tbl1 AS t1DECLARE @tbl1 TABLE( ID INT, Descr VARCHAR(25))

DECLARE @tbl2 TABLE( ID INT, Descr VARCHAR(25))

 INSERT INTO @tbl1

 SELECT 1, 'test1' UNION ALL

 SELECT 2, 'test2' UNION ALL

 SELECT 3, 'test3'

INSERT INTO @tbl2

 SELECT 1, 'test1 additional'  UNION ALL

 SELECT 1, 'test1 additional2' UNION ALL

 SELECT 2, 'test2 additional'  

--===== Concatenate values in tbl2 and add them to values in table 1

 SELECT t1.ID,

		t1.Descr + ISNULL((SELECT ',' + t2.Descr FROM @tbl2 t2 WHERE t1.id = t2.id FOR XML PATH('')),'')

FROM @tbl1 AS t1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 4

Hello everyone, I really appreciate your help with this. I'm starting to think I'm in the wrong job. I've tried all suggestions and can't get them to work with my tables. Here are the two tables I'm working with. can I humbly ask someone to show me how to use any of the solutions using the data structure from my tables. I know this is asking a lot, but I am so lost right now.

Table 1:

JOBID

SERVICEORDERNUM

ORIGINALDUEDATE

DUEDATE

JOBSTATUS

DISPATCHUNITID

STATUSUPDATEDBY

JOBTYPE

DISPATCHREGIONID

WORKGROUP

DA

NAME

CUSTPHONENO

Table 2:

jobid

pldetails

planalysis

analyzed_by

update_time

From Table1 I'd like to join Table2 on the jobid and then concatenate each update from the pldetails and analysis columns to themselves. So there may be multiple updates for a jobid and I'd like to add them all to one line combining data from both tables.

So for jobid 'a' the pldetails and analysis column will look something like this:

pldetails planalysis

details1, details2, details3 analysis1, analysis2, analysis3

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 5

I would use two loops to solve this issue. The first loop is the mastr and contains the information for table 1. the second would loop around table b for all rows that matches the jobid of table a. The second loop would contain an update and set clause setting the current value of the field equal to the current value of the field + the new value grapped from the cursor or poor man's loop. I'm not sure if there is a quicker way to do the same thing, but let me know if you want me to construct the T-SQL for you to accomplish this the way I'd do it.

Marvin

Marvin Dillard
Senior Consultant
Claraview Inc

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Marcus Farrugia wrote:

can I humbly ask someone to show me how to use any of the solutions using the data structure from my tables. I know this is asking a lot, but I am so lost right now.

Absolutely not a problem... but, let's stop "plinking"... read the following article and come back with some real tables and data we can test with...

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

You also need to include an example of what you'd like the output to be... exactly... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

... and no... I don't recommend using two loops 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 8

Marcus Farrugia

SSCarpal Tunnel

Points: 4718

December 19, 2007 at 10:41 am

#761479

Ok thanks Jeff, I'm reading the article you posted now and will reply as instructed.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

Marvin,

I have to agree with Jeff. This or no other query should be done with a loop unless you cannot avoid it. To put the performance impact into perspective, I generated two tables with 1 million records each, I used a loop because I do not have a copy of Jeff's temp table TSQL :w00t:. I compared the time it took to process my cursor and David's XML and the results speak for themselves. I was not surprised by the results, but now know the power of XML string concatenation.

The cursor:

The cursor processed 1 million rows in 5 minutes and 27 seconds.

The XML

The XML processed 1 million rows in 24 seconds.

My blog: http://jahaines.blogspot.com

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 10

Hi Jeff,

I originally didn't want to come right out and ask for someone to write my query for me, so I apologize if I was vague or "plinking" to begin with.

Here are the table designs from the tables I actually want to use. There are actually three tables that I want to join or get data from in order to create a query for a report that I will eventually output using reporting services.

So the first two tables 1. #PLOrders 2. #MCommAnalysis are one liners, I just want to join the data together to get a one line per item data set. Then I want to add table 3. #MCommDetails and take the pldetails and planalyis columns which have multiple entries per jobid and concantenate each pldetails and planalysis entry to combine all into one cell. So for instance re:

jobid 'CT2007333085545-1'

will look like this.

'CT2007333085545-1','BUS-SL/REP','A','905C ML','STRSVILLE','STRSVILLEPEARLO','South','9058191239','Nov 29 2007 9:00PM','Nov 30 2007 12:00PM','Nov 29 2007 5:53PM','Nov 29 2007 9:47AM','R16','sro CT2007333076948-1 (R15 Refer to Cable Terminal F2 cp Trouble #1239):R16 Other reason not listed load to bus tech','Nov 29 2007 8:04PM', 'Accounting', 'Tim', details1 & details2 & details3, 'analysis1 & analysis2 & analysis3'

As instructed in your article below is the script with the table creations and data insertions.

IF OBJECT_ID('#PLOrders') IS NOT NULL

DROP TABLE #PLOrders;

create table #PLOrders

(

jobid varchar(20),

jobtype varchar(20),

workgrpid varchar(50),

dispatchregionid varchar(50),

dispatchareaid varchar(50),

NPA varchar(50),

custphoneno varchar(50),

jobtypeid varchar(50),

OriginalDueDate datetime,

duedate datetime,

TimeOfCode datetime,

Troublereceiveddate datetime,

troublecategory varchar(50),

reasoncode varchar(25),

comments varchar(2000),

timeofdump datetime

)

SET DATEFORMAT DMY

insert into #PLOrders

(jobid, jobtypeid, troublecategory, workgrpid, dispatchregionid, dispatchareaid, npa,

custphoneno, OriginalDueDate, duedate, TimeOfCode, Troublereceiveddate,

reasoncode, comments, timeofdump)

SELECT 'CT2007333085545-1','BUS-SL/REP','A','905C ML','STRSVILLE','STRSVILLEPEARLO','South','9058191239','Nov 29 2007 9:00PM','Nov 30 2007 12:00PM','Nov 29 2007 5:53PM','Nov 29 2007 9:47AM','R16','sro CT2007333076948-1 (R15 Refer to Cable Terminal F2 cp Trouble #1239):R16 Other reason not listed load to bus tech','Nov 29 2007 8:04PM' UNION ALL

SELECT 'CT2007333085591-1','DEMAND OPM','1','BRC 519W','CHATHAM','CHATHAMO','West','5193529105','Nov 29 2007 7:00PM','Nov 30 2007 5:00PM','Nov 29 2007 6:15PM','Nov 26 2007 8:28PM','R2','spke to mr KIPROWSKI, rdd 071130. mgr aware:TRADED FOR A PATTERN','Nov 29 2007 8:04PM' UNION ALL

SELECT 'SA2007331028113-1','BUS ML','1','905E ML','OSHAWA','OSHAWAO','South','LCLXXU150578-000BLCA-000','Nov 24 2007 11:44AM','Nov 29 2007 12:00PM','Nov 28 2007 3:19PM','Nov 22 2007 11:46AM','T16','T3 Geo Coding Problem this is in general motors and needs bell dedicated tech km1457','Nov 28 2007 3:35PM' UNION ALL

SELECT 'CT2007332055512-1','DEMAND OPM','1','519W ANM','WIND CORE','TECUMSEHO','West','5197350029','Nov 28 2007 10:00PM','Nov 28 2007 5:00PM','Nov 28 2007 8:22PM','Nov 27 2007 9:51PM','R2','left msg on 5197350061 re RDD 20071129:C3 Outside of Appt. Interval v','Nov 28 2007 8:34PM' UNION ALL

SELECT 'CT2007332068427-1','DEMAND OPM','A','905 W DMD','STRSVILLE','STRSVILLEPEARLO','South','9058132605','Nov 28 2007 6:00PM','Nov 28 2007 6:00PM','Nov 28 2007 8:32PM','Nov 28 2007 7:05AM','R2','pls rdd for 11-29am duty Chris Faith aware','Nov 28 2007 8:34PM'

if OBJECT_ID('#MCommAnalysis') is not null

drop table #MCommAnalysis

create table #MCommAnalysis

(

jobid varchar(50),

acc_dept varchar(50),

acc_manager varchar(50),

update_time datetime

)

insert into #MCommAnalysis

(jobid, acc_dept, acc_manager, update_time)

select 'CT2007333085545-1', 'Accounting', 'Tim', 'Nov 28 2007 5:22PM' union all

select 'CT2007333085591-1', 'Sales', 'Marcus', 'Nov 28 2007 11:25PM' union all

select 'SA2007331028113-1', 'Operations', 'Jason', 'Nov 28 2007 7:25PM' union all

select 'CT2007332055512-1', 'HR', 'Jodie', 'Nov 30 2007 8:42PM' union all

select 'CT2007332068427-1', 'IT', 'Danny', 'Nov 27 2007 9:51PM'

if OBJECT_ID('#MCommDetails') IS NOT NULL

DROP TABLE #MCommDetails

create table #MCommDetails

(

jobid varchar(50),

pldetails varchar(250),

planalysis varchar(250),

analyzed_by varchar(50),

update_time varchar(50),

)

insert into #MCommDetails

(jobid, pldetails, planalysis, analyzed_by, update_time)

select 'CT2007333085545-1', 'details1', 'analysis1', 'Marcus', 'Nov 27 2007 9:51PM' union all

select 'CT2007333085545-1', 'details2', 'analysis2', 'Marcus', 'Nov 27 2007 10:38PM' union all

select 'CT2007333085545-1', 'details3', 'analysis2', 'Dave', 'Nov 27 2007 11:23PM' union all

select 'CT2007333085591-1', 'details1', 'analysis1', 'Jason', 'Nov 30 2007 12:00PM' union all

select 'CT2007333085591-1', 'details2', 'analysis2', 'Danny', 'Nov 30 2007 8:42PM' union all

select 'CT2007333085591-1', 'details3', 'analysis3', 'Vince', 'Nov 30 2007 7:24PM' union all

select 'CT2007333085591-1', 'details4', 'analysis4', 'Marcus', 'Nov 30 2007 4:45M' union all

SELECT 'SA2007331028113-1', 'details1', 'analysis1', 'Keith', 'Nov 28 2007 8:32PM' union all

SELECT 'SA2007331028113-1', 'details2', 'analysis2', 'Rob', 'Nov 28 2007 7:25PM' union all

SELECT 'CT2007332055512-1', 'details1', 'analysis1', 'Marcus', 'Nov 28 2007 7:25PM' union all

SELECT 'CT2007332055512-1', 'details2', 'analysis2', 'Jodie', 'Nov 28 2007 6:235PM' union all

SELECT 'CT2007332055512-1', 'details3', 'analysis3', 'Tim', 'Nov 28 2007 11:25PM' union all

SELECT 'CT2007332068427-1', 'details1', 'analysis1', 'Tim', 'Nov 28 2007 3:22PM' union all

SELECT 'CT2007332068427-1', 'details2', 'analysis2', 'Aaron', 'Nov 28 2007 5:22PM'

David McFarland SSChampion Points: 11815 More actions · Answer 11

This should work with your sample data. You get to add the rest of the columns from the two main tables to the select, as I'm lazy, in case you haven't heard 🙂 :

SELECT



	po.jobid

	,ca.acc_dept

	,Stuff(Replace((SELECT ' & ' + cd.pldetails

					FROM #MCommDetails AS cd

					WHERE po.jobid = cd.jobid

					FOR XML PATH('')),'&','&'),1,3,'') AS pldetails

	,Stuff(Replace((SELECT ' & ' + cd.planalysis

					FROM #MCommDetails AS cd

					WHERE po.jobid = cd.jobid

					FOR XML PATH('')),'&','&'),1,3,'') AS planalysis

FROM

	#PLOrders AS po

	INNER JOIN #MCommAnalysis AS ca ON

		po.jobid = ca.jobid

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

See what I mean, Marcus? No... no one wrote 100% of the code you needed... but, unless I miss my guess, David's code will be very easy for you to modify. He had all the info he needed and all the data he needed to come up with a correct answer very quickly because of your last post. You can just bet that when you post the self-populating data like you did, the answers will come quickly and will probably be tested.

Anyway, you all set now?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 13

Yes, I definately see, and it makes a lot of sense in terms of making it easier for people helping me to help me, also having done that it also makes it a lot easier for me to follow the solutions. Lesson learned. 🙂

David, I've just tried your code out and it seems to be working great, I'm just going to go back and adjust it to add all the columns I need to add and spend some time analyzing it in order to comprehend it as well.

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 14

Marcus Farrugia

SSCarpal Tunnel

Points: 4718

December 19, 2007 at 1:57 pm

#761559

David, yes the code works great! Thanks to everyone who took the time to help me with this. It was a good learning experience for me, and now I can go home, relax and forget about work for the next 16 hours.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

Perfect... thanks for the feedback. And nice job, David!

Having 16 hours off is a rarity in this business 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions