none
How to Change Column Name In a Query used another table Column Name ? RRS feed

  • Question

  • HI All

    See My Code Please

    SELECT * FROM [Sal].[Pays] WHERE ChooseField='F1'

    And Get this result

    

    Now another Table

    SELECT F1 FROM [Sal].[SalaryList]   
    And Get this result

    Now , How to change F1 to OverTime?

    please help me.

    thanks all


    Name of Allah, Most Gracious, Most Merciful and He created the human

    Friday, July 12, 2019 1:39 PM

All replies

  • SELECT F1 as [OverTime] FROM [Sal].[SalaryList]   
    Friday, July 12, 2019 1:45 PM
    Moderator
  • There is no way in TSQL to dynamically change the column name.  Column names must be known at compile time.

    Friday, July 12, 2019 1:50 PM
    Moderator
  • DECLARE @F1 NVARCHAR(25)
    
    SELECT @F1 = PaysDetail FROM [Sal].[Pays] WHERE ChooseField = 'F1'
    
    SELECT @F1
    
    
    
    SELECT F1  AS @F1
    
     FROM [Sal].[SalaryList]

    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near '@F1'.
    


    Name of Allah, Most Gracious, Most Merciful and He created the human

    Friday, July 12, 2019 1:59 PM
  • Object names like column alias have to start with an underscore or a letter, other caharcters are not allowed.

    Your Approach won't work, column names must be fix, you can not make the dynamic


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 12, 2019 2:07 PM
  • You have to use dynamic sql.

    Create table [Pays](PaysDetail NVARCHAR(25),ChooseField NVARCHAR(25))
    insert into [Pays] values('OverTime','F1')
    Create table [SalaryList] (F1 decimal(10,2))
    insert into [SalaryList] values(109)
    
    DECLARE @F1 NVARCHAR(25)
    SELECT @F1 = PaysDetail FROM [Pays] WHERE ChooseField = 'F1'
     
    
     declare @sql nvarchar(2000)
     Set @sql=N'SELECT F1 as ' +quotename(@F1)+' FROM [SalaryList]'
     --exec (@sql)
     EXEC sp_executesql @sql
     
    drop table [Pays], [SalaryList]

    Friday, July 12, 2019 2:16 PM
    Moderator
  • Hello,

    How to change F1 to OverTime?

    SELECT 

         <list your comma-separated fields here, except ChooseField>

       , 'OverTime' As "ChooseField"

    FROM .... 


    Sincerely, Highly skilled coding monkey.


    Friday, July 12, 2019 3:02 PM
  • > Column names must be known at compile time.

    He have column names. 


    Sincerely, Highly skilled coding monkey.

    Friday, July 12, 2019 3:07 PM
  • Maybe redesign your database. The second table will have two basic columns: the first one will contain “F1” as text (or numeric equivalent in case of limited range of values), the second will contain the value. Then you can join the tables in your queries.

    Friday, July 12, 2019 3:36 PM
  • I believe the OP is asking for:

    SELECT F1 as [Sal].[Pays].[PaysDetail] FROM [Sal].[SalaryList]

    That is not possible.

    Friday, July 12, 2019 6:20 PM
    Moderator
  • Dynamic SQL is not needed actually. Insert the data into a temp table. Then use sp_rename to change the column names.

    INSERT #temp (...)    SELECT ....

    tempdb..sp_rename '#temp', 'col', @F1, 'COLUMN'


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 12, 2019 9:40 PM
  • Hi sh2019,

     

    I'm not quite sure about your request, whether you wanna change the column name from 'F1' to 'Overtime' directly or change the column name in [salary list] table to the name which is the value of 'paysdetail' in [pay] table.

     

    If it is the latter one, I pivot the [pay] table first in order to add one column , then 'join' 'Paysdetail' which corresponds to 'choosefield', and finally achieve the goal you want.

     

    Could you please provide some more sample data so that we are able to test the results?

    IF OBJECT_ID('Pays') IS NOT NULL drop table Pays
    IF OBJECT_ID('SalaryList') IS NOT NULL drop table SalaryList
    create table Pays ( 
    [HasInsurance] int,
    [HasTax] int,
    [PaysDetail] varchar(20),
    [IsWorkOrder] int,
    [ChooseField] varchar(5)
    )
    create table SalaryList (
    id int,
    F1 int,
    F2 int)
    go
    insert into Pays values
    (0,0,'OverTime',1,'F1')
    insert into SalaryList values
    (1,70749,11111)
    
    
    ---If there's only one value 'overtime'in [PaysDetail]
    insert into Pays values
    (0,0,'OverTime',1,'F2')
    
    ;with cte as
    (select a.ChooseField,a.Salary
    from SalaryList
    unpivot 
    (salary for ChooseField in(F1,F2))a) 
    
    select c.*,b.salary as [OverTime] from 
    cte b
    join Pays c
    on b.choosefield= c.choosefield
    ----and.... (other conditions)
    /*
    HasInsurance HasTax      PaysDetail           IsWorkOrder ChooseField OverTime
    ------------ ----------- -------------------- ----------- ----------- -----------
    0            0           OverTime             1           F1          70749
    0            0           OverTime             1           F2          11111
    */
    
    
    ---If there're more values in [PaysDetail]
    delete [Pays] where [ChooseField]='F2'
    insert into Pays values
    (0,0,'ABCCDEDD',1,'F2')
    
    ;with cte as(
    select c.*,b.salary from 
    (select a.ChooseField,a.Salary
    from SalaryList
    unpivot 
    (salary for ChooseField in(F1,F2))a) b
    join Pays c
    on b.choosefield= c.choosefield
    ----and.... (other conditions) 
    )
    select * from cte
    pivot
    (sum(salary)for [PaysDetail]in
    ([OverTime],[ABCCDEDD]))as pvt
    
    /*
    HasInsurance HasTax      IsWorkOrder ChooseField OverTime    ABCCDEDD
    ------------ ----------- ----------- ----------- ----------- -----------
    0            0           1           F1          70749       NULL
    0            0           1           F2          NULL        11111
    */
    

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 15, 2019 6:46 AM
  • Most likely he asking about TRANSFORM statement. As far as I remember - it's available on Access and not available on MS SQL.

    So, there are two options:

    - use Access as ActiveX object and it TRANSFORM statement

    - use procedure which emulate TRANSFORM statement.

    No reason to guess until TS explain what he need. 


    Sincerely, Highly skilled coding monkey.

    Monday, July 15, 2019 8:09 AM