Stored Procedure Combining Cell Information

I have a table that holds the features of vehicles. It has two fields. UNTID and DES1. The UNTID field holds the stock number of the vehicle and the DES1 field holds the description of the feature. Here is and example:


UNTID         DES1

123456        Air Conditioning
123456        Cruise Control
154690        Air Conditioning

As you can see a vehicle can have more than one row in the table. Here in lies the problem. I need to extract from the table the information in this format:


123456    Air Conditioning, Cruise Control

I am running MS SQL 2000. I am attempting to do this in a stored procedure and have the results be available in a table with just the UNTID and DES1 on one line per vehicle.

I thought if I could create a temp table and run through a select statement and then add the information into the temp table in the order I wanted it that would work. But I get stuck on the looping through the select statement. Am I doing this the right way or is there an easier way to do this?

3
jhawk3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ksaulCommented:
You could create a simple function to concatenate by ID and then use that in your select:

CREATE FUNCTION [dbo].[fGetDescriptions](@ID int)
RETURNS VARCHAR(1000) AS

BEGIN
DECLARE @List varchar(1000)
   SELECT @List = COALESCE(@List + ', ', '') + DES1
   FROM Vehicles --Change to the actual name of your table
   WHERE UnitID = @ID

   RETURN @List
END

GO

---
SELECT DISTINCT ID, dbo.fGetDescriptions(ID)
FROM Vehicles --change to your table name
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jhawk3Author Commented:
Your function did combine all of the UntId's into one table that eliminated doubles. Now how would I go about using that to combine the features. Would I still procede with my original idea? I would still need to loop through the original table.

3
0
ksaulCommented:
What is the Name of the table?  The column name that you want to combine in a comma delimited set of values for each id is DES1?  Is that correct?  Or are their other fields to consider?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jhawk3Author Commented:
The table name is VHVINFEA. There are other fields in the table but those are the only two that I am concerned with. The column names are UntId and Des. Yes, Des is the column that I want to combine into a comma delimited list.

3
0
ksaulCommented:
The intention of the function I posted was to concatenate all the descriptions.  Here it is with the exact table names and column names.  This should give you the results you want.

ALTER FUNCTION [dbo].[fGetDescriptions](@ID int)
RETURNS VARCHAR(1000) AS

BEGIN
DECLARE @List varchar(1000)
   SELECT @List = COALESCE(@List + ', ', '') + DES
   FROM VHVINFEA --Change to the actual name of your table
   WHERE UnitID = @ID

   RETURN @List
END

GO

---
SELECT DISTINCT UnitID, dbo.fGetDescriptions(UnitID) AS Features
FROM VHVINFEA --change to your table name

It doesn't need a loop because when you select multiple varchar row values into a variable like this it will combine them in the variable:

DECLARE @Combine varchar(2000)
SET @Combine = ''
SELECT @Combine = @Combine + AnyColumn
FROM AnyTable
SELECT @Combine
0
jhawk3Author Commented:
I did exactly what you have written. However, when I go to get the information it only returns a list of the UntIds with no duplicates.
0
Christopher KileCommented:
Try changing this:

SELECT DISTINCT UnitID, dbo.fGetDescriptions(UnitID) AS Features
FROM VHVINFEA --change to your table name

To this:

 ---
SELECT D.UnitID, dbo.fGetDescriptions(D.UnitID) AS Features
FROM
(SELECT DISTINCT UnitID FROM VHVINFEA) AS D

0
ksaulCommented:
What are the field types for UnitID and Des?

When you run this to get the data does anything show up in the Features column?
SELECT DISTINCT UnitID, dbo.fGetDescriptions(UnitID) AS Features
FROM VHVINFEA
0
jhawk3Author Commented:
Sorry ksaul, I missed the "AS Features" part of the SELECT statement. It works perfectly. One more question, don't worry you are getting the points, can I join a table in the same function?

There is another table that has information in it about weather the vehicle is in stock or not. It also has a field named UntId that can join with VHVINFEA. Is there away to join the table "VHVIN" and only return vehicles that are in stock?

Fields in VHVIN

UntId = stock number
Status = 333
Marketability =  166

These values and fields make a vehicle in stock.

3
0
ksaulCommented:
Sure you could join like this
SELECT DISTINCT f.UnitID, dbo.fGetDescriptions(f.UnitID) AS Features,  v.Status, v.Marketability
FROM VHVINFEA f
INNER JOIN VHVIN v ON f.UnitID = v.UnitID

But, the function will list the features for any UnitID.  So it might be simpler to:

SELECT UnitId, Status, Marketability, dbo.fGetDescriptions(UnitID)
FROM VHVIN
0
jhawk3Author Commented:
ksaul, Thanks I appreciate all of the help.

3
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.