June 6, 2003 at 7:24 am
Scenario: Parent Table and Child table
Needs: for each parent row, make a string consisting of one of the child rows columns so that only one resulting parent row is returned with the string as the last column:
ie think of customers as parent and orders as child
The columns in the return set should be something like:
cust_number,cust_name,cust_phone,cust_order_numbers
so some sample data for a customer with 6 orders would look like:
"H012", "Amber Customer","9025551234","1247 1248 1262 1264 1270 1398"
how can I create a select to do this without using a stored proc - just pure SQL?
Thanks,
caper
CaperSailor
Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977
June 6, 2003 at 8:19 am
a UDF would be the best way to go
CREATE FUNCTION dbo.UF_ReturnOrders (@Cust_num int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @RetStr varchar(255)
SELECT @RetStr = ISNULL(@RetStr + ' ' + CONVERT(varchar(20),Order_numbers),CONVERT(varchar(20),Order_numbers))
FROM Orders
WHERE (Cust_num = @Cust_num)
RETURN @RetStr
END
SELECT *,dbo.UF_ReturnOrders(cust_number) as cust_numbers
FROM customers
June 6, 2003 at 8:31 am
Yes, that does seem the easiest - I was just thinking that I wasn't forgeting something in SQL that would allow me to do it completely in-line...something along the lines of CUBE - but again, I wasn't thinking clearly on the matter...
Thanks so much!
CaperSailor
Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977
June 6, 2003 at 8:38 am
actually - i have come across the same problem many times myself - and have always opted for this solution . it would be interesting to see if this can be done in one single sql statement - without using UDFs - any takers??
June 9, 2003 at 3:55 am
Why not solving it with a nested select instead of the UDF? Preformance shouldn't be so much different though...
Hans
June 9, 2003 at 4:07 am
But you cannot (at least I have not seen it done) create a single nested SQL statement to return a single string representing multiple rows. The UDF apporach is all I am aware of.
June 9, 2003 at 4:23 am
true - the only SQL only options i can think of are restrictive and would not be true solutions
Hans - can you show us what kind of nested select you have got
June 9, 2003 at 6:08 am
Sorry guys :/ I was too hasty in responding, I had not tested my solution before proposing it. And you are right, it seems quite difficult trying to solve it with a nested query.
Meanwhile I will keep trying but I guess I will not succeed.
Hans
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply