?
Solved

Group multiple records into one record

Posted on 2007-07-20
9
Medium Priority
?
382 Views
Last Modified: 2010-03-20
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?
0
Comment
Question by:cgcmq
  • 6
  • 3
9 Comments
 

Author Comment

by:cgcmq
ID: 19533573
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
 
LVL 8

Expert Comment

by:k_rasuri
ID: 19533635
--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
 

Author Comment

by:cgcmq
ID: 19533705
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 8

Expert Comment

by:k_rasuri
ID: 19533828
'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
 

Author Comment

by:cgcmq
ID: 19533915
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
 

Author Comment

by:cgcmq
ID: 19533990
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
 

Author Comment

by:cgcmq
ID: 19534146
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
 
LVL 8

Accepted Solution

by:
k_rasuri earned 2000 total points
ID: 19534599
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
 

Author Comment

by:cgcmq
ID: 19900707
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question