want to get the output in a column

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

General SQL Server Forums

New to SQL Server Programming

want to get the output in a column

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-12 : 06:49:53

hi all,,,

i got the output like this
title user_id
4 0
2 1
4 1
4 0
3 0
4 1
6 0

i want to get whole out ina single column with comma seperation

O/P should be like this

40,21,41,40,30,41,60

how i can do this

Regards,
Divya

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-12 : 06:58:00

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 07:08:44

[code]declare @result varchar(8000)

select @result=coalesce(@result+ ',','') + cast(title as varchar(15)) + cast(user_id as varchar(15))
from yourtable

select @result
[/code]

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-12 : 07:12:48

yeyeye .. got the answer.. thank u visakh

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 07:17:36

welcome

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 07:44:09

@visakh16

I seem to remember reading that that concatenate-to-variable approach might not work from SQL2005 onwards? Perhaps it had to do with an ORDER BY being included too, I've forgotten. Anything you've come across in that regard?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-12 : 08:16:53

if i am selecting from two tables its throwing error

declare @result1 varchar(8000)
select @result1= (coalesce(@result1+ ',','') + (select title from FIS_User) + (select user_id from FIS_User_Community) )
from FIS_User u
inner join FIS_User_Community uc
on uc.User_ID= 285 and u.Title = 5
select @result1

error is in subquery

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

Regards,
Divya

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 08:26:04

"if i am selecting from two tables its throwing error "

Yeah, that isn't what visakh16 recommended

Try this:


declare @result1 varchar(8000)
select @result1= (coalesce(@result1+ ',','') + COALESCE(title, 'No title')
       + ' - ' + COALESCE(CONVERT(varchar(20), user_id), 'No User ID'))
from FIS_User u
inner join FIS_User_Community uc
on uc.User_ID= 285 and u.Title = 5
select @result1

Edit: Fixed typo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 10:12:37

quote:
Originally posted by Kristen

@visakh16

I seem to remember reading that that concatenate-to-variable approach might not work from SQL2005 onwards? Perhaps it had to do with an ORDER BY being included too, I've forgotten. Anything you've come across in that regard?

Sorry Kristen I've not yet come across anything like that myself.

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:04:35

Searched high and low and can't find anything, so I expect I've completely mis-remembered it. Sorry about that ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:05:25

quote:
Originally posted by Kristen

Searched high and low and can't find anything, so I expect I've completely mis-remembered it. Sorry about that ...

No problem

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-12 : 12:21:31

quote:
Originally posted by Kristen
I've completely mis-remembered it. Sorry about that ...

That's a good one to take to court...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:30:04

So you can't rememebr it either eh? You playing for the defence, or the offence?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-12 : 12:30:59

so how we can do this.... ?????

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:34:14

quote:
Originally posted by divyaram

so how we can do this.... ?????

Regards,
Divya

Did you try Kristen's soln?

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:57:53

Here it is again, in case it got confused in the chit-chat:


declare @result1 varchar(8000)
select @result1= (coalesce(@result1+ ',','') + COALESCE(title, 'No title')
       + ' - ' + COALESCE(CONVERT(varchar(20), user_id), 'No User ID'))
from FIS_User u
inner join FIS_User_Community uc
on uc.User_ID= 285 and u.Title = 5
select @result1

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:58:47

P.S> Personally I would move "u.Title = 5" out of the JOIN and put it in a WHERE. The result is the same, so its just semantics.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 13:02:32

quote:
Originally posted by Kristen

P.S> Personally I would move "u.Title = 5" out of the JOIN and put it in a WHERE. The result is the same, so its just semantics.

Nice to see back in form today

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 13:43:37

When I went away you weren't even a student in the form, now you're the teacher already

But that's just me being picky, and semantics, because I'm sure you meant "on form"

I'd better get my flame-proof suit, because I'm dyslexic and some bright spark will be along to find pedantic fault with my post now that no matter how many timers I proofread it I won't find

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 13:54:16

quote:
Originally posted by Kristen

When I went away you weren't even a student in the form, now you're the teacher already

But that's just me being picky, and semantics, because I'm sure you meant "on form"

I'd better get my flame-proof suit, because I'm dyslexic and some bright spark will be along to find pedantic fault with my post now that no matter how many timers I proofread it I won't find

Yeah I know that Kristen

I'm just a humble follower of you

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 15:04:28

Hahaha ... "humble follower" indeed

... that reminds me of when I was in Japan as a young man. I'm a lazy tyke so did "Nothing" in terms of studying the language before I got there. But even just saying "Good morning" in Japanese got people to reply "Wow! Your Japanese is amazing, isn't it" ("Aa, Anata no Nihongo ga jouzu desu neh!"). So I got to Japan, discovered that it would be nice to take a girl out to the cinema (I was 18

...) and set about Learning Japanese and after flogging my guts out for a year, by which time I was pretty fluent, I was still getting exactly the same "Aa, Anata no Nihongo ga jouzu desu neh!" reply - indeed, after two years when I really was fluent I got the exact same reply.

Of course being a Brit. I got fed up with this, and concocted some replies in Japanese such as "Not at all, I'm still a copywriter" - which was way too clever for the Japanese folk as "copywriting" was such an ancient skill that the Japanese word itself was not in common use, not to mention that the irony of a copywriter needing to be perfect at Kanji was well and truly lost on them. Gave me a chuckle though!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources