Solved

Stored Procedure Combining Cell Information

Posted on 2007-04-05
11
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
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

627 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