examnple of using do while and cursors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

examnple of using do while and cursors

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-19 : 11:18:34

i Name

1 Broker-Dealer
2 Insurance/Reinsurance Co.

I have the above temp table. I need to concatenate the column Name as

'Broker-Dealer Insurance/Reinsurance Co.'.

I tried using CASE and PIVOT. Neither worked. I was told I could use CURSOR or do while loop.

Can you help on how I can achieve the string concatenation.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 11:21:49

There are only two rows in the table?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:22:36

[code]SELECT STUFF((SELECT ' '+ Name FROM yourtable FOR XML PATH('')),1,1,'')[/code]

EDIT: fixed wrong delimiter
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-19 : 11:27:04

quote:
Originally posted by visakh16

SELECT STUFF((SELECT '/'+ Name FROM yourtable FOR XML PATH('')),1,1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Always fast to post an answer

But the slash is coming from the data itself and not from the concatenating solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-19 : 11:29:19

No there will be more than two rows. max of 46 rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:30:07

quote:
Originally posted by sqlnovice123

No there will be more than two rows. max of 46 rows.

But you want all to be concatenated dont you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-19 : 11:30:13

What is the XML path in this case?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:30:33

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

SELECT STUFF((SELECT '/'+ Name FROM yourtable FOR XML PATH('')),1,1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Always fast to post an answer
But the slash is coming from the data itself and not from the concatenating solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

Ok fixed it in original suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-19 : 11:30:41

Yes all to be concatenated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:31:35

quote:
Originally posted by sqlnovice123

Yes all to be concatenated.

then my suggestion should be enough

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-19 : 11:34:09

Awesome, that worked. Thanks a bunch!

SELECT STUFF((SELECT '/'+ ClientTypeValue FROM #ClientTypeList FOR XML PATH('')),1,1,'').

So even if I have 44 rows, I assume I should see a concatenated string of all 44 column values.

Broker-Dealer/Insurance/Reinsurance Co.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:39:32

it would work...see for yourself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources