Collections:
Assigning New Column Names in a View in SQL Server
How To Assign New Column Names in a View in SQL Server?
✍: FYIcenter.com
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 fyi_links_dump AS SELECT CONVERT(VARCHAR(20),id) + ', ' + CONVERT(VARCHAR(20),counts) + ', ''' + url + '''' FROM fyi_links WHERE counts > 1000 GO Msg 4511, Level 16, State 1, Procedure fyi_links_dump, Line 2 Create View or Function failed because no column name was specified for column 1. CREATE VIEW fyi_links_dump (Line) AS SELECT CONVERT(VARCHAR(20),id) + ', ' + CONVERT(VARCHAR(20),counts) + ', ''' + url + '''' FROM fyi_links WHERE counts > 1000 GO SELECT TOP 3 * FROM fyi_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'
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.
⇒ Determining Data Types of View Columns in SQL Server
⇐ Deleting Data from a View in SQL Server
2016-11-03, 1633🔥, 0💬
Popular Posts:
How To Drop a Stored Procedure in Oracle? If there is an existing stored procedure and you don't wan...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...