SQL Question

[xloop]

Hi

I've got a fairly straight forward problem. Given the statemtent and results below:

SELECT DISTINCT dbo.tblTaskStock.StockID, dbo.tblTaskNames.TaskName
FROM dbo.tblTaskNames INNER JOIN
dbo.tblTasks ON dbo.tblTaskNames.TaskID = dbo.tblTasks.TaskID INNER JOIN
dbo.tblTaskStock ON dbo.tblTasks.ID = dbo.tblTaskStock.TaskID

3166 Delivery
3166 Hotstage
3167 Delivery
3167 Hotstage

What im looking for are these results:

3166 Delivery,Hotstage
3167 Delivery,Hotstage

Not sure how this is done.

Thanks

 

[Smarty]

Don't know the correct answer, but this should help you away with it. Try a subselect in the FROM clause!

LIKE:

SELECT DISTINCT dbo.tblTaskStock.StockID, dbo.tblTaskNames.TaskName, 3rd colom
FROM (SELECT * FROM dbo.tblTaskNames INNER JOIN
dbo.tblTasks ON dbo.tblTaskNames.TaskID = dbo.tblTasks.TaskID INNER JOIN
dbo.tblTaskStock ON dbo.tblTasks.ID = dbo.tblTaskStock.TaskID)

 

[Smarty]

Can you give some more information:
* tables with there elements
* examples of what is in the tables

 

[BogdanRo]

In SQL Server 2000 first you must create a function SSum (string sum):
CREATE FUNCTION SSum (@id AS INT)
RETURNS VARCHAR(255) AS
BEGIN

DECLARE @text AS VARCHAR(255)
DECLARE @rez AS VARCHAR(255)
DECLARE crs CURSOR
FOR SELECT TaskName FROM ... WHERE StockID = @id

OPEN crs
FETCH NEXT FROM crs INTO @text
SET @rez = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rez = @rez + LTRIM(RTRIM(@text)) + ','
FETCH NEXT FROM crs INTO @text
END

CLOSE crs
DEALLOCATE crs

RETURN SUBSTRING(@rez,1,LEN(@rez)-1 )

After, you may use this function :

SELECT DISTINCT StockID, dbo.SSum( StockID ) AS Tasks
FROM ...

or

SELECT StockID, dbo.SSum( StockID ) AS Tasks
FROM ...
GROUP BY StockID

 

[xloop]

BogdanRo - Excellent.

Thanks!