Solved

Stored Procedure Combining Cell Information

Posted on 2007-04-05
11
227 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:jhawk3
  • 5
  • 5
11 Comments
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18859545
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
 

Author Comment

by:jhawk3
ID: 18859672
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
 
LVL 10

Expert Comment

by:ksaul
ID: 18859719
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:jhawk3
ID: 18859757
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
 
LVL 10

Expert Comment

by:ksaul
ID: 18859867
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
 

Author Comment

by:jhawk3
ID: 18860012
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
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18860089
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
 
LVL 10

Expert Comment

by:ksaul
ID: 18860095
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
 

Author Comment

by:jhawk3
ID: 18860169
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
 
LVL 10

Expert Comment

by:ksaul
ID: 18860221
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
 

Author Comment

by:jhawk3
ID: 18861080
ksaul, Thanks I appreciate all of the help.

3
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 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