Solved

Stored Procedure Combining Cell Information

Posted on 2007-04-05
11
207 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now