MS SQL Server Concepts and Programming Question:
Download Questions PDF

How To Assign New Column Names in a View?

Answers:

Answer #1
By default, column names in a view are provided by the underlying SELECT statement.

But sometimes, the underlying SELECT statement can not provide names for output columns that specified as expressions with functions and operations. In this case, you need to assign new names for the view's columns. The tutorial exercise below creates a view to merge several table columns into a single view column with a format called CSV (Comma Separated Values):

CREATE VIEW ggl_links_dump AS
SELECT CONVERT(VARCHAR(20),id)
+ ', ' + CONVERT(VARCHAR(20),counts)
+ ', ''' + url + ''''
FROM ggl_links WHERE counts > 1000
GO
Msg 4511, Level 16, State 1, Procedure ggl_links_dump,
Line 2
Create View or Function failed because no column name
was specified for column 1.


Answer #2
<pre>CREATE VIEW ggl_links_dump (Line) AS
SELECT CONVERT(VARCHAR(20),id)
+ ', ' + CONVERT(VARCHAR(20),counts)
+ ', ''' + url + ''''
FROM ggl_links WHERE counts > 1000
GO

SELECT TOP 3 * FROM ggl_links_dump
GO
Line
------------------------------------------------------------
7600, 237946, ' eyfndw jdt lee ztejeyx l q jdh k '
19437, 222337, ' eypx u x'
55924, 1877, ' eyq ntohxe i rtnlu riwaskzp cucoa dva c rc'
</pre>
The first CREATE VIEW gives you an error, because the SELECT statement returns no column for the concatenated value, and no view column name is specified explicitly.

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
Can We Delete Data from a View?How Column Data Types Are Determined in a View?