asked on

Group multiple records into one record

I have an SQL Server database that contains the following three tables:

MS_LocalSources which holds information about companies such as name and address
MS_Categories which contains a list of company types
MS_DistCatJoin which relates the MS_LocalSources primary key (id) to one or more records in the MS_Categories table

I need to find each category that a company is associated with. Normally this would simply be a matter of:

SELECT t1.Lname, t2.catid
FROM MS_LocalSources T1
LEFT JOIN MS_DistCatJoin t2 ON t1.id = Distid
WHERE t1.id = 8

which would return:

Lname            catid
ACME Inc      2
ACME Inc      5
ACME Inc      9

However, in this instance I would like to have only 1 record per company returned with a field that contains each catid as a string:

ACME Inc      2, 5, 9

Can this de done and if so how?

cgcmq

ASKER

Sorry, but I need to expand on this question. I found that a method to achieve this is:

DECLARE @strValues varchar(20)
SELECT @strValues = COALESCE(@strValues+',', '') + catid
FROM
(
      SELECT t1.Lname, t2.catid
      FROM MS_LocalSources T1
      LEFT JOIN MS_DistCatJoin t2 ON t1.id = Distid
      WHERE T1.ID = 5
) X
ORDER BY catid

SELECT [catid] = @strValues

Now what I need is to fit this into the rest of my original query.

SELECT province, city, lname, catid
FROM (MS_LocalSources T1
LEFT JOIN MS_Prov t2 ON t1.prov = t2.prov)
Left join MS_DistCatJoin t3 on t1.id = Distid
WHERE t1.prov = 'ab'
ORDER BY province, city, tag, lname

I need to replace the catid in the above select with the COALESCE statement.

k_rasuri

--create temp table
SELECT t1.Lname, t2.catid INTO #TEMP
FROM MS_LocalSources T1
LEFT JOIN MS_DistCatJoin t2 ON t1.id = Distid
WHERE t1.id = 8

-- create function
CREATE FUNCTION dbo.GetRecords(@LName varchar(60))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @res VARCHAR(8000)
SELECT @res = COALESCE(@res + ', ' , '') + ISNULL(catid, '')
FROM #TEMP
WHERE lname = @lname
RETURN (@res)
END

-- Using function
SELECT DISTINCT Lname, dbo.GetRecords(LName) as Catid from #temp
go

--drop table when you are done
drop table #temp

cgcmq

ASKER

I am getting a couple of errors on this code:

'CREATE FUNCTION' must be the first statement in a query batch.
Must declare the scalar variable "@lname".
A RETURN statement with a return value cannot be used in this context.

k_rasuri

'CREATE FUNCTION' must be the first statement in a query batch - Not necessary
Must declare the scalar variable "@lname" - may be case sensistive issue. change @LName to @lname
A RETURN statement with a return value cannot be used in this context - dont know why it throwed this error

Try executing one piece at a time and see

cgcmq

ASKER

I tried changing the captialization on Lname with no success.

The create temp table works fine. Then when executing the create function block I get this error:

Server: Msg 2772, Level 16, State 1, Procedure GetRecords, Line 9
Cannot access temporary tables from within a function. I confirmed that the temp table does exist.

I am logged into the database as other than the dbo user. Does the user in the CREATE FUNCTION have to be chagned to match my login?

cgcmq

ASKER

I also tried changing it from a temp table to a regular table. Now it give me an error at the SELECT clause of the Using Function:

Incorrect syntax near the keyword 'SELECT'.

cgcmq

ASKER

I modified the query to create a normal table and changed the dbo.GetRecords to reflect the actual user and it works. However, I have run into another problem. I changed the opening SELECT clause to retrieve all records from a given location:

--create table
SELECT t1.id ,province, city, lname, catid INTO TEMP_OUT
FROM (MS_LocalSources T1
LEFT JOIN MS_Prov t2 ON t1.prov = t2.prov)
Left join MS_DistCatJoin t3 on t1.id = Distid
WHERE t1.prov = 'NU'
ORDER BY province, city, lname

There happens to be more than one company in this location with the same name (multiple franchises).

I need to delete the function and recreate it based on the primay key - ID.

ASKER CERTIFIED SOLUTION

k_rasuri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cgcmq

ASKER

My apologies as I forgot that I left this question open. I appreciate your time and, again, I am sorry for not closing this off.