none
In query get column name from another table - how? RRS feed

  • Question

  • I have a simple query on a table:

    select ID, [Name], Tier1Price, Tier2Price from Products

    I have another table of ProductTiers

    ID     TierName

    1       Wholesale

    2       Retail

     

    I want to change my query so the result table uses the Tier names as column names:

    ID, [Name], WholeSale, Retail

    How can I go about acheiving this?  Do I have to use a 3rd table that links product to the tier (vs fixed columns) or is there a quick and dirty way to do this? Thanks.

    Thursday, February 24, 2011 11:37 PM

Answers

  • You will need a dynamic SQL:

    declare @SQL nvarchar(max)
    
    select @SQL = 'select [ID], [Name], Tier1Price as ' +
    quotename((select top 1 TierName from ProductTiers where ID = 1 order by ID))
    + ', Tier2Price as ' + quotename((select top 1 TierName from ProductTiers where ID = 2 order by ID)) + ' FROM Products'
    
    print @SQL -- testing
    
    execute (@SQL)
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Heems Friday, February 25, 2011 3:22 PM
    Friday, February 25, 2011 12:29 AM
    Moderator

All replies

  • SELECT

     

    ID,Name,

     

    MAX(CASE WHEN ID = 1 THEN Tiername ELSE 0 END) AS Wholesale

     

    MAX(CASE WHEN ID = 2 THEN Tiername ELSE 0 END) AS Retail

    Thursday, February 24, 2011 11:55 PM
  • SELECT

     

    ID,Name,

     

    MAX(CASE WHEN ID = 1 THEN Tiername ELSE 0 END) AS Wholesale

     

     

    MAX(CASE WHEN ID = 2 THEN Tiername ELSE 0 END) AS Retail


    The ID refered to in the case statement is the one in the ProductTiers table and not int he Products Table.  So i don't think the query would work as written...
    Thursday, February 24, 2011 11:58 PM
  • i started looking into the 3rd intermediary table.. i think i need a pivot but not quite sure how do it.

    So table 1 (Products):

    ID, Name

    table 2 (Tiers)

    ID, TierName

    table 3 (ProductTiers)

    ID, ProductID, TierID, TierPrice

    What's the pivot to get the result table:

    Products.ID, Products.Name, Tier1Name, Tier2Name, ...

    where Tier1Name, Tier2Name, etc. columns hold the TierPrices for the given product and tier?  Thanks.

    Friday, February 25, 2011 12:04 AM
  • You will need a dynamic SQL:

    declare @SQL nvarchar(max)
    
    select @SQL = 'select [ID], [Name], Tier1Price as ' +
    quotename((select top 1 TierName from ProductTiers where ID = 1 order by ID))
    + ', Tier2Price as ' + quotename((select top 1 TierName from ProductTiers where ID = 2 order by ID)) + ' FROM Products'
    
    print @SQL -- testing
    
    execute (@SQL)
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Heems Friday, February 25, 2011 3:22 PM
    Friday, February 25, 2011 12:29 AM
    Moderator