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?
cgcmqAsked:
Who is Participating?
 
k_rasuriConnect With a Mentor Commented:
if you want to recreate the function using id, just rename @lname to @id.....

CREATE FUNCTION dbo.GetRecords(@ID int)
RETURNS VARCHAR(8000)
AS
BEGIN
 DECLARE @res VARCHAR(8000)
 SELECT @res = COALESCE(@res + ', ' , '') + ISNULL(catid, '')
 FROM TEMP_OUT t
 WHERE t.ID = @lD
 RETURN (@res)
END

--execute function
SELECT DISTINCT ID, dbo.GetRecords(ID) as Catid from TEMP_OUT
0
 
cgcmqAuthor Commented:
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.
0
 
k_rasuriCommented:
--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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
cgcmqAuthor Commented:
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.
0
 
k_rasuriCommented:
'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
0
 
cgcmqAuthor Commented:
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?
0
 
cgcmqAuthor Commented:
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'.
0
 
cgcmqAuthor Commented:
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.
0
 
cgcmqAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.