CODEWITHSUNDEEP
sqlsql-servert-sql
stoner
stoner

Reputation: 407

How to dynamize column alias from another table

I have the bellow query ,i'm looking for to dynamize column name label from another table. In table #Tmp2 the Dim1 change depending Dim1.Num for exemple i can have for Dim1.Num = 1 => Dim1 =BusinessUnit
in other cas i can hav Dim1.Num = 1 => Dim1 =Department 

SELECT Dim1.Dim1 ,Dim1.ValueDim1  ,
       Dim2.Dim2 ,Dim2.ValueDim2  ,
       #Tmp1.Amount

INTO   #Tmp3

FROM   #Tmp1

INNER JOIN #Tmp2 as Dim1 on Dim1.id = #Tmp1.id And Dim1.Num=1
INNER JOIN #Tmp2 as Dim2 on Dim1.id = #Tmp1.id And Dim2.Num=2

This is the result :

Dim1            ValueDim1  Dim2         ValueDim2  Amount
BusinessUnit    002        Department   027        1000

But i want this result 

BusinessUnit    ValueDim1  Department   ValueDim2  Amount
BusinessUnit    002        Department   027        1000

That is, depending on what value of #Tmp2.Num, the applicable name of the column can change.

mybe somthing like this 

SELECT Dim1.Dim1 AS (select Dim from #Tmp2 where #Tmp2.Num = 1)

Upvotes: 2

Views: 411

Answers (1)

KidCode
KidCode

Reputation: 4201

You could use Dynamic SQL:

DECLARE @ColName VARCHAR(20) = (select Dim from #Tmp2 where #Tmp2.Num = 1)

EXEC('SELECT Dim1.Dim1 ['+@ColName+'] ,Dim1.ValueDim1  ,
       Dim2.Dim2 ,Dim2.ValueDim2  ,
       #Tmp1.Amount

INTO   #Tmp3

FROM   #Tmp1

INNER JOIN #Tmp2 as Dim1 on Dim1.id = #Tmp1.id And Dim1.Num=1
INNER JOIN #Tmp2 as Dim2 on Dim1.id = #Tmp1.id And Dim2.Num=2')

The limitations with this (and your question in general) is that if your #Tmp2 has two records with the same Num, then the query will throw a Subquery returned more than one value error. The only way to get around this is to change your @ColName query so it only ever returns a single value:

DECLARE @ColName VARCHAR(20) = (select TOP 1 Dim from #Tmp2 where #Tmp2.Num = 1)

Update:

If it's possible that your query to get the column name can return 0 records, then modify your query as follows:

DECLARE @ColName VARCHAR(20) = (
    select Dim from #Tmp2 where #Tmp2.Num = 1 
    UNION 
    SELECT 'DefaultValue' WHERE NOT EXISTS (select Dim from #Tmp2 where #Tmp2.Num = 1)
    )

However, if your query returns a record but the value in the Column you want to use as the Column Name is null, then modify the query like so:

DECLARE @ColName VARCHAR(20) = (select ISNULL(Dim,'DefaultValue') from #Tmp2 where #Tmp2.Num = 1)

Upvotes: 2

Related Questions