Return records in a variable - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Return records in a variable

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-30 : 17:28:23

Hello,

I have 3 tables:
[Blogs] > BlogId (PK), ...
[Posts] > PostId (PK), BlogId(FK), PostTitle, ...
[Labels] > LabelId (PK), PostId (FK), LabelName, ...

I am getting all posts given a BlogId.

My problem is:
I need to add a column, named PostLabels, to the returned table.

For each record, the value of column PostLabels should be:
Label1,Label2,Label3,Label4, ...

This labels are all the LabelNames found in Labels for each PostId.

How can I do this?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 17:40:48

Try this

SELECT DISTINCT	l1.PostID,
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + l2.LabelName FROM Labels AS l2 WHERE l2.PostID = l1.PostID ORDER BY ',' + l2.LabelName FOR XML PATH('')), 1, 1, '') AS PostLabels
FROM		Labels AS l1
ORDER BY	l1.PostID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 17:43:06

How long have you been working on this blog application now?

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-30 : 18:04:40

quote:
Originally posted by Peso

How long have you been working on this blog application now?

Peter Larsson
Helsingborg, Sweden

Hi Peter,

I started 2 weeks ago. It is almost done.
I am working on 4 applications for .NET. One is the blogs.
The other 3 are: FAQ, Documents (Documents Storing) and Mailing List.

I usually develop the Asp.Net and C#/VB.Net parts.
But this time I am also doing the SQL code because I will use these applications for my personal projects.

It is the SQL that gives me more problems.

I know that there are a few applications out there.
However, by creating my own I am able to customize it and make any changes that I need or want to.

Thanks,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources