Solved

Stored Procedure Combining Cell Information

Posted on 2007-04-05
11
245 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 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