locked
How to use select * but rename selected column names RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I want to create a view using select * from table name, but I need the column names in the view to be renamed. Currently, I do this

    create view dbo.Table1_V
    as

    SELECT   Id, Name as TestName from Table1 ...

    Is there any way to have column named TestName in a result set but using select * (i.e. without listing all columns in select statement).

    Thank you.

    Isolda

    Friday, September 14, 2012 7:54 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    You can do it but the renamed column(s) will double up:

    USE AdventureWorks2012;
GO
CREATE VIEW vProductTest
AS
SELECT Name AS ProductName, * FROM Production.Product;
GO

SELECT * FROM vProductTest;
GO
/*

ProductName	ProductID	Name	ProductNumber
Adjustable Race	1	Adjustable Race	AR-5381
Bearing Ball	2	Bearing Ball	BA-8327
BB Ball Bearing	3	BB Ball Bearing	BE-2349
Headset Ball Bearings	4	Headset Ball Bearings	BE-2908
....
*/

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Proposed as answer by James Still Saturday, September 15, 2012 7:25 AM
    • Marked as answer by Isolda Monday, September 17, 2012 1:20 PM
    Saturday, September 15, 2012 7:15 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    No, we do not have this feature in the language, neither in the UI.

    If you think this is something that could help to improve the code process, then feel free to file a suggestion.

    https://connect.microsoft.com/

    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Naomi N Sunday, September 16, 2012 5:30 AM
    Friday, September 14, 2012 8:00 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    What you might want to do is generate the view creation from information_Schema.

    Friday, September 14, 2012 8:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Isolda, there is no way to rename/alias columns without doing it at the individual column level (as in your example).  

    Thanks,
    Sam Lester (MSFT)

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Friday, September 14, 2012 8:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    There is no way to do this, how it's possible to tell select statement to select different names for columns without specifying it explicitly? You can probably change the column names in the actual table :-)

    Mark it as helpful if so!!! thanks, Mithilesh

    • Proposed as answer by Rajeev Harbola Sunday, September 16, 2012 7:22 AM
    Saturday, September 15, 2012 1:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    No, but a good SQL programmer NEVER uses "SELECT * " in production code so why is this a problem? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Sunday, September 16, 2012 5:31 AM
    Saturday, September 15, 2012 2:48 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hello Isolda.

    The  first issue or Problem is "We can't Rename Column's names in Sql."

    to solve your problem you can insert Present Table to another table that column names is your favorite names.

    then create view based on new table.

    Same Below:

    create table NewTable (id int,TestName nvarchar(10))
--==================================================
Insert into NewTable(id,NewName)
        select (id,name) 
        from OldTable

    • Proposed as answer by SQL IT Sunday, September 16, 2012 6:26 AM
    Saturday, September 15, 2012 7:00 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can do it but the renamed column(s) will double up:

    USE AdventureWorks2012;
GO
CREATE VIEW vProductTest
AS
SELECT Name AS ProductName, * FROM Production.Product;
GO

SELECT * FROM vProductTest;
GO
/*

ProductName	ProductID	Name	ProductNumber
Adjustable Race	1	Adjustable Race	AR-5381
Bearing Ball	2	Bearing Ball	BA-8327
BB Ball Bearing	3	BB Ball Bearing	BE-2349
Headset Ball Bearings	4	Headset Ball Bearings	BE-2908
....
*/

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Proposed as answer by James Still Saturday, September 15, 2012 7:25 AM
    • Marked as answer by Isolda Monday, September 17, 2012 1:20 PM
    Saturday, September 15, 2012 7:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you all for reply. The information_Schema suggestion looks interesting, I'll look into it. But for now SQLUSA's solution seems the easiest. Thanks! 

    Isolda

    Monday, September 17, 2012 3:13 PM