how to return multiple records from a view in sql server 2008

Question

i have a view which presently returning single record when called....i like it to return three records may be upon inner joining a different view or temp table...as those records share the same data except single column... im not sure about this plz some one throw light on this please..thank you in advance

Answer 1

As I understand it, you have a view that currently returns 1 row, but you need it to return 3 rows - and the rows are to be collected from 3 different tables that contains different columns. Correct? Anyway..

Say that we, for instance, have the following tables:

[table1] (title,age)
[table2] (title,born)
[table3] (title,sex)

You could return all data in the same resultset (even though the columns diff):

SELECT title,age,'' born,'' sex FROM table1
UNION ALL
SELECT title,'',born,'' FROM table2
UNION ALL
SELECT title,'','',sex FROM table3

And this is how that result would look like:

title  age  born  sex
Abc    31         
Bca         1978     
Cab               M