January 6, 2008 at 6:49 am
Hi I need to turn a column with values 1,2,3 into a recordset (with i've done with a cursor - would be interested to find a better solution), and then back into to a column with 'dog cat fish' displaying along with the customer's id. Can this be done with out considerable slowdown? and if so how?
My customers table holds 8000 records. The lpg colmn generally holds no more than 2 id's.
Hope you can help
r
Customer (table)
customerid lpg
1 1,2,3
2 3
lpg table
lpgid description
1 dog
2 cat
3 fish
January 6, 2008 at 9:48 am
Why would you turn it into a recordset and than back to a CSV? Why not just move the CSV from one side to another.
January 6, 2008 at 11:02 am
Hi,
Thanks for responding...
I need the result comma separated as otherwise i would have the customer line in my recordset repeated 2/3 times depending upon the number of values in the column and I require 1 row per customer.
The query is for an export and the customer would not understand what 1 2 or 3 are, that's why i need it to display text in a col. Can you help?
I need the result to look like
Customerid Description..... + other cols from customer table
1 Dog, Cat
2 Fish
3 Cat, Fish
January 6, 2008 at 6:42 pm
A user-defined function would be a solution for this.
Have the function take in a list of integers, put that list into a table variable, then join that table to the descriptions table to get a CSV list back.
-- Setup of the tables
CREATE TABLE customer
(
customerid int primary key,
lpg varchar(10)
)
CREATE TABLE lpg
(
lpgID int primary key,
description varchar(100)
)
GO
--
CREATE FUNCTION fn_ID2Desc
(
@idlist VARCHAR(10)
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @TempList TABLE (myID int)
-- Step 1: Add code to put values in @idlist to the @TempList table
-- Code left out for brevity
-- Step 2: Return descriptions as a CSV list
DECLARE @desclist VARCHAR(500)
SELECT @DescList = COALESCE(@DescList + ', ', '') + o.description
FROM lpg AS o
JOIN @TempList t ON o.lpgID = t.myid
RETURN(@Desclist)
END
GO
Here's how to the call function.
SELECT CustomerID, dbo.fn_ID2Desc(lpg) FROM Customer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply