Building Single Field With Results Of Multiple Queries

[BoulderBum]

If I have tables A and B which have fields x and y respectively, how can I construct a view or query such that I stick every record of A.x and every record of B.y into a single resulting field?

For instance, if A.x has:

1, 3, 5

and B.y has:

2, 3, 4

I want the result of the query to contain:

1, 2, 3, 4, 5

in a single column.

 

[RiverGuy]

Try this:

DECLARE @Result NVARCHAR(4000)
SET @Result = ''

SELECT @Result = @Result + ',' + X
FROM Table1 A WHERE Table1ID = (SELECT Table1ID FROM  Table1 B WHERE A.Table1ID = B.Table1ID)


SELECT @Result = @Result + ',' + Y
FROM Table2 A WHERE Table2ID = (SELECT Table2ID FROM  Table2 B WHERE A.Table2ID = B.Table2ID)

SELECT @Result = RIGHT(@Result, LEN(@Result) -1)

SELECT @Result AS Result