Paul Allen


Hi,

I am hoping someone can help me with a problem i have with getting the correct resultset.

I am creating a stored procedure which will display projects and their associated categories. Here is the table structure:

Projects table:

USE [PDFreelance]

GO

/****** Object: Table [dbo].[tblProjects] Script Date: 07/28/2007 19:57:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblProjects](

[iProjID] [int] IDENTITY(1273,1) NOT NULL,

[guidUserName] [uniqueidentifier] NOT NULL,

[varTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[varDescription] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[varBudget] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[varStatus] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('Open'),

[varAttach] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dtCreated] [datetime] NOT NULL DEFAULT (getdate()),

[dtExpires] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

[iProjID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[tblProjects] WITH CHECK ADD FOREIGN KEY([guidUserName])

REFERENCES [dbo].[aspnet_Users] ([UserId])

And the project categories table:

USE [PDFreelance]

GO

/****** Object: Table [dbo].[tblProjectCat] Script Date: 07/28/2007 19:59:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblProjectCat](

[iProjID] [int] NOT NULL,

[iCatID] [int] NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblProjectCat] WITH CHECK ADD FOREIGN KEY([iCatID])

REFERENCES [dbo].[tblCategories] ([iCatID])

GO

ALTER TABLE [dbo].[tblProjectCat] WITH CHECK ADD FOREIGN KEY([iProjID])

REFERENCES [dbo].[tblProjects] ([iProjID])

I also have another table that stores the category names.

Now, each project may be linked to up to 3 categories and must have a minimum of 1 category linked.

My question is, how do I display a resultset such as...

iProjID, varTitle, varBudget, Category1, Category2, Category3 (all categories to be included in the same column, seperated by comma's)

Hope this makes sense, if you need any more explanation pelase let me know!

As a pointer, i want the results to come out the same as here: http://www.GetACoder.com




Re: Need help with query

hunchback


Hi Paul,

Here you will find the answer.

Concatenating row values in Transact-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB