none
Select columns with alias that comes from other table valus

    Question

  • hi,

    I have 2 tables on same DB:

    Fact table: CustomerID, GenericField1, GenericField2, GenericField3..

    1001 1 0 1

    Fields table: CustomerID, GenericFieldName,     DisplayName

    1001 GenericField1      Registrations

    i want to build a select statement that will select the CustomerID, and data from the "fact" table

    and where  Fact.GenericField1 equals to Fields.GenericFieldName.Value I want the Alias for GenericField1 to be its value from Fields table.

    Is this possible? I  have more than 40 Generic Fields...

    Thanks,

    Tuesday, October 15, 2013 8:37 AM

Answers

  • Hi

    PFB code,

    Create table fact1
    (
    Customerid int, 
    GenericField1 int,
    GenericField2 int,
    GenericField3 int
    )
    GO
    create table fact2
    (
    Customerid int,
    GenericFieldName varchar(30),
    Displayname varchar(40)
    )
    GO
    insert into fact1
    select 1001,1,0,1
    Insert into fact2
    select 1001,'GenericField1','Display1' union all
    select 1001,'GenericField2','Display2' union all
    select 1001,'GenericField3','Display3' 
    Declare @sql varchar(max) = 'Select '
    select @sql = @sql + GenericFieldName + ' AS ' + Displayname+',' From fact2
    select @sql = @sql + 'Customerid From fact1 where customerid =1001'
    exec( @sql)

    Thanks

    Saravana Kumar C

    Tuesday, October 15, 2013 9:06 AM

All replies

  • Did not get your actual requirement, but I think you are trying to achieve what is termed as Pivoting.

    Check this link: Pivoting on TechNet


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 15, 2013 8:42 AM
  • Hi

    PFB code,

    Create table fact1
    (
    Customerid int, 
    GenericField1 int,
    GenericField2 int,
    GenericField3 int
    )
    GO
    create table fact2
    (
    Customerid int,
    GenericFieldName varchar(30),
    Displayname varchar(40)
    )
    GO
    insert into fact1
    select 1001,1,0,1
    Insert into fact2
    select 1001,'GenericField1','Display1' union all
    select 1001,'GenericField2','Display2' union all
    select 1001,'GenericField3','Display3' 
    Declare @sql varchar(max) = 'Select '
    select @sql = @sql + GenericFieldName + ' AS ' + Displayname+',' From fact2
    select @sql = @sql + 'Customerid From fact1 where customerid =1001'
    exec( @sql)

    Thanks

    Saravana Kumar C

    Tuesday, October 15, 2013 9:06 AM