none
dynamic column alias from another table RRS feed

  • Question

  • Hi there,

    I have two tables : ADDRESSES and Fieldnames

    In Addresses : Text1, Text2, Text3, Date1, Date2

    In Fieldnames: Text1 = Company, Text2 = Street, Text3 = Zip, Date1 = CreateDate, Date2 = Birthday

    I need to create a sql-Statemt like select * from ADDRESSES that gives me automatically the real Fieldnames instead of the internal column-names.

    Is there any solution for that?

    Help would be fine

    Thanks in advance

    Sunday, September 9, 2012 8:03 AM

Answers

  • >> I need to create an SQL statement like select * from ADDRESSES that gives me automatically the real Field_names instead of the internal column-names. <<

    So much, so wrong, in so few words. First of all, columns in a row in a table are not fields in a record. A column has a known name, data type and constraints (these define its domain). No competent SQL programmer would try to do this. 

    You can kludge this thing in a pile of garbage with dynamic SQL, but why? 

    CREATE TABLE Companies
    (company_duns CHAR(9) NOT NULL PRIMARY KEY,
     company_naem VARCHAR(35) NOT NULL,
     company_street_addr VARCHAR(35) NOT NULL,
     zip CHAR(5) NOT NULL
       CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]')0;

    Your "creation_date" is audit data and does not belong here. A company does not have a birthdate. 

    Please, please read any book on databases, and basic programming. 



    --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

    Sunday, September 9, 2012 7:31 PM

All replies

  • SELECT * will not work.

    Nonetheless, you can use dynamic SQL for column list alias preparation:

    http://www.sqlusa.com/bestpractices/dynamicsql/


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

    Sunday, September 9, 2012 8:33 AM
    Moderator
  • Try:

    CREATE TABLE Addresses (Text1 VARCHAR(100), Text2 VARCHAR(100), Text3 CHAR(5), 
    Date1 DATE, Date2 DATE)
    
    CREATE TABLE FieldNames (RawName VARCHAR(20), RealName VARCHAR(20))
    INSERT INTO FieldNames 
    SELECT 'Text1', 'Company'
    UNION ALL SELECT 
    'Text2','Street'
    UNION ALL SELECT 
    'Text3','Zip'
    UNION ALL SELECT 
    'Date1','CreateDate'
    UNION ALL SELECT 
    'Date2','Birthday'
    
    
    -- Query begins here
    DECLARE @SQL NVARCHAR(MAX)
    
    SELECT @SQL = STUFF(( SELECT ', ' + QUOTENAME(C.COLUMN_NAME) + ' AS ' + QUOTENAME(F.RealName)
    FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN FieldNames F ON C.COLUMN_NAME = F.RawName
    WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = 'Addresses'
    ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')
    
    SELECT @SQL = 'SELECT ' + @SQL + ' FROM Addresses'
    
    PRINT @SQL
    
    EXECUTE(@SQL)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, September 9, 2012 2:52 PM
    Moderator
  • >> I need to create an SQL statement like select * from ADDRESSES that gives me automatically the real Field_names instead of the internal column-names. <<

    So much, so wrong, in so few words. First of all, columns in a row in a table are not fields in a record. A column has a known name, data type and constraints (these define its domain). No competent SQL programmer would try to do this. 

    You can kludge this thing in a pile of garbage with dynamic SQL, but why? 

    CREATE TABLE Companies
    (company_duns CHAR(9) NOT NULL PRIMARY KEY,
     company_naem VARCHAR(35) NOT NULL,
     company_street_addr VARCHAR(35) NOT NULL,
     zip CHAR(5) NOT NULL
       CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]')0;

    Your "creation_date" is audit data and does not belong here. A company does not have a birthdate. 

    Please, please read any book on databases, and basic programming. 



    --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

    Sunday, September 9, 2012 7:31 PM