Produce a list of users and whose houses they rent
-
Ideally would like output like this
The following live in a house owned by Bob
Sam , Sill and Bill
The following live in a house owned by George
Stella and Gem etc
Table A
owner House#
Bob 1
George 2
Paul 3
Table B
renter house#
sam 1
sill 1
bill 1
gem 2
stella 2
Mike 3
Thank you for your time
-
How's this?
--function
go
create function dbo.fnGetHouseRenters(@House int) returns varchar(100) as
begin
declare @x varchar(100)
select @x = isnull(@x + ', ', '') + renter from B where House = @House
if charindex(',', @x) > 0
begin
declare @finalCommaPosition int
set @finalCommaPosition = len(@x) - charindex(',', reverse(@x))
set @x = stuff(@x, @finalCommaPosition + 1, 1, ' and')
end
return @x
end
go
--data
create table A (owner varchar(10), House int)
insert A
select 'Bob', 1
union all select 'George', 2
union all select 'Paul', 3
create table B (renter varchar(10), House int)
insert B
select 'Sam', 1
union all select 'Sill', 1
union all select 'Bill', 1
union all select 'Gem', 2
union all select 'Stella', 2
union all select 'Mike', 3
--calculation (1) - what you requested
select 'The following live in a house owned by ' + owner + char(13) + char(10) + dbo.fnGetHouseRenters(House) + char(13) + char(10) + char(13) + char(10) from A
--calculation (2) - simpler, and do the formatting at the front-end
select owner, dbo.fnGetHouseRenters(House) from A
--calculation (3) - simplest, and do the formatting at the front-end
select owner, renter from B inner join A on b.House = a.House
--tidy up
go
drop table A
drop table B
drop function dbo.fnGetHouseRenters
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
SELECT r.Renter,o.Owner
FROM A r,B o
WHERE r.House#=o.House
If a person is having two houses then how can u differentiate?
Thanks,
-
Damn, Ryan beat me to it, agian
My solution anyway
CREATE FUNCTION dbo.udf_renters (@house int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @result varchar(1000)
SELECT @result = COALESCE(@result+' , ','') + renter
FROM TableB
WHERE house = @house
IF CHARINDEX(',',@result) > 0
SET @result= STUFF(@result,LEN(@result)-CHARINDEX(',',REVERSE(@result))+1,1,'and')
RETURN @result
END
SELECT *, dbo.udf_renters(house)
FROM TableA
Far away is close at hand in the images of elsewhere.
Anon. -
I'm sure you'll beat me next time!
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply