Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Assigning New Column Names in a View

By: FYIcenter.com

(Continued from previous topic...)

How To Assign New Column Names in a View?

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.

(Continued on next topic...)

  1. What Are Views?
  2. How To Create a View on an Existing Table?
  3. How To See Existing Views?
  4. How To Drop Existing Views from a Database?
  5. How To Get a List of Columns in a View using "sys.columns"?
  6. How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
  7. How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
  8. How To Generate CREATE VIEW Script on an Existing View?
  9. How To Get the Definition of a View Out of the SQL Server?
  10. Can You Create a View with Data from Multiple Tables?
  11. Can You Create a View using Data from Another View?
  12. What Happens If You Delete a Table That Is Used by a View?
  13. Can You Use ORDER BY When Defining a View?
  14. How To Modify the Underlying Query of an Existing View?
  15. Can You Insert Data into a View?
  16. Can You Update Data in a View?
  17. Can You Delete Data from a View?
  18. How To Assign New Column Names in a View?
  19. How Column Data Types Are Determined in a View?
  20. How To Bind a View to the Schema of the Underlying Tables?
  21. How To Create an Index on a View?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...