Help me form this SQL? - SQL Server Forums

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

Help me form this SQL?

Author

Topic

Christopher Weeks
Starting Member

4 Posts

Posted - 2006-03-02 : 17:45:42

Hi all, this'll be my first post here, hopefully I'll get this right and someone can help.

I'm trying to generate a subquery that will take a set of one-column rows and concatenate them into a single cell.

I've adapted from this article (http://www.sqlteam.com/item.asp?ItemID=2368) the following code:

DECLARE @topicList varchar(200)

SELECT @topicList = COALESCE(@topicList + ', ', '') + MAB_topics.topic_name
FROM MAB_topics

SELECT @topicList

that produces a list of all the topic_names in the database seperated by commas. That's fine.

I couldn't follow all the code in this thread (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=18363) but I was able to extract and modify this code:


DECLARE @topicList varchar(200)

SELECT MAB_topics.topic_name
INTO #tmp
FROM MAB_topics, MAB_articleIndex
WHERE 	MAB_articleIndex.article_ID = 76 AND
	MAB_topics.topic_ID = MAB_articleIndex.topic_ID

SELECT * FROM #tmp

drop table #tmp

which returns the two topic_names that I'd expect. (Note: this is my very first day declaring variables and using temp tables.) So I keep thinking that the following code would combine the two above working functions. But, when I run it in Query Analyzer, it just returns "(2 row(s) affected)" instead of a recordset.


DECLARE @topicList varchar(200)

SELECT MAB_topics.topic_name
INTO #tmp
FROM MAB_topics, MAB_articleIndex
WHERE 	MAB_articleIndex.article_ID = 76 AND
	MAB_topics.topic_ID = MAB_articleIndex.topic_ID

SELECT @topicList = COALESCE(@topicList + ', ', '') + #tmp.topic_name
FROM #tmp

drop table #tmp

What am I doing wrong? I'm sure it's something simple but I've been reading and trying and reading and trying for a few hours and haven't gotten it yet. The ideal response would figure out what I'm trying to do as well as what my mistake is and explain how I'm misthinking. A close second would be just fixing my code. :)

Thanks in advance,

Chris

Christopher Weeks
Starting Member

4 Posts

Posted - 2006-03-02 : 20:43:54

Um...<blush>...nevermind.

SELECT @topicList

near the end did the trick.

But! If you have a better way to do this, then still feel free to reply.

Christopher Weeks
Starting Member

4 Posts

Posted - 2006-03-02 : 20:57:00

Rats, I can't edit that last post. Anyway, you could also reply if you know how I can insert that code into a pair of parentheses and make it a subquery.

Thanks,

Chris

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-02 : 23:49:57

Consider creating your formula as a user-defined function:

Create function dbo.Topic_List(@Article_ID int)
returns varchar(200) as
begin

DECLARE @topicList varchar(200)

SELECT	@TopicList = COALESCE(@topicList + ', ', '') + MAB_topics.topic_name
from	MAB_topics
	inner join MAB_articleIndex on MAB_topics.topic_ID = MAB_articleIndex.topic_ID
where	MAB_articleIndex.article_ID = @Article_ID

return	@topicList

end

Then you can user the function in any SELECT query:

select	Article_ID,
	dbo.Topic_List(Article_ID)
from	[SomeTable]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-03 : 01:37:28

Also read this for more information
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Christopher Weeks
Starting Member

4 Posts

Posted - 2006-03-03 : 08:45:22

Thanks guys!

Madhivanan, I had read that article when I first started searching for a way to do this, but there was so much that I hadn't learned that it didn't mean much to me. Thanks for pointing me back there because it was more useful this time around.

Blindman, thanks for leading me to my first user-defined function too. That was just the ticket!

(It's kind of funny that I worked for a couple years as a .NET programmer doing really valuable data-manipulation stuff for my organization with only the most rudimentary SQL knowledge. It's really remarkable how much you can do with this stuff.)

Chris

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-03-06 : 04:29:54

Chris I think one of the keys to SQL is to think in terms of set based operations rather than thinking linearly. It can be a bit confusing at first but when you get used to it you will find it can be incredibly powerful.

steve

-----------

Oh, so they have internet on computers now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources