create view from joined tables having same column names

[uncleroydee]

I need to combine the data from two joined tables in a view. Some of the columns have the same name and I would like to merge the data from those two columns into one column in the view keeping that name.

Following is some paraphrasing of the page from BOL that contains the syntax for creating a view. Can anyone clarify the "n is a placeholder" statement for me? That seems to be the syntax I need to use.

CREATE VIEW (T-SQL)
.
.
.

Syntax
CREATE VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
select_statement
[WITH CHECK OPTION]

Arguments
view_name
Is the name of the view. View names must follow the rules for identifiers. Specifying the view owner name is optional.

column
Is the name to be used for a column in a view. Naming a column in CREATE VIEW is necessary only when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns may otherwise have the same name (usually because of a join), or when a column in a view is given a name different from that of the column from which it is derived. Column names can also be assigned in the SELECT statement.
If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.
.
.
.

n
Is a placeholder indicating that multiple columns can be specified.

Thanks,

Roy

 

[foxdev]

n is a placeholder just means that you can put multiple column names in there - "n" is not a literal.

I would like to merge the data from those two columns into one column in the view keeping that name

This sounds more like a UNION than a JOIN. In the case of UNIONs, the first column name used for the specified column number becomes the final column name.

If its a JOIN, its probably just as easy to use the AS clause in the SELECT statement to assign column names. For example, if you wanted to keep both columns:

[tt]select MyColA, Table1.MyColB as FirstName,
Table2.MyColB as SecondName ... [/tt]


Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[tlbroadbent]

n is a placeholder simply means that you can have multiple columns in the view - col1, col2, col3,.... n. Or n represents coln.

Your view syntax is very simple. You only need to list the duplicated column once.

Example:

create view MyView As

Select a.ID,a.Name,a.Address,a.Phone,b.SSN,b.Dept,b.Job
From TblA a Join TblB b On a.ID=b.ID

Note that ID is in both tables but only appears once in the selection list. Terry

http://members.home.net/tlbroadbent/index.htm

 

[uncleroydee]

Sometime we get too creative for our own good!

The two tables originally had the same structure but I deleted the unused columns from one of the tables. Then I got hung up on the join. It would be very easy for me to reinsert the columns and use a union! And since the reinserted columns would only contain nulls there wouldn't be any performance or table size issues.

Thanks, Robert!