Select column name dynamically based on rows from another table in SQL server

Question

I need to make below situation. I need a.DATE_TIME field to find right column name in parentheses. Because there are columns for each days and I need calculate day from SHIFT_TABLE. Column names start with 'TPR' plus day like TPR13

DECLARE @SQLText NVARCHAR(MAX) = ''
SELECT  @SQLText += 'SELECT (SELECT '
SELECT  @SQLText += ( 'TPR' +  Convert(varchar,DATEPART(day,a.DATE_TIME) )
        ) +
        ' from SHIFT_TABLE as s Where s.id = a.id ) from MAIN_TABLE as a where a.id=''1'' and a.ay=''12'' and a.yil=''2018''  '

print @SQLText

I take error message: The multi-part identifier "a.DATE_TIME" could not be bound.

SHIFT_TABLE occurs id and TPR01,TPR02,...TPR31 columns MAIN_TABLE occurs id, ay, yil and DATE_TIME columns

I have to calculate day from a.DATE_TIME.

As a result I try to reach like this, SELECT (SELECT TPR13 from SHIFT_TABLE as s Where s.id = a.id ) from MAIN_TABLE as a where a.id='1' and a.ay='12' and a.yil='2018'

How can I solve it? Many Thanks,

You can probably use UNPIVOT instead of writing such a query. Post the tables' schema, some sample data and what you expect as the output. BTW you don't use any other table in the query right now. You a have a single statement that contatenates strings and contains a Convert(varchar,DATEPART(day,a.DATE_TIME) ) function call. There are no tables involved here, which is why you get that error. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 13, 2018 at 10:40
Or you could use a CASE WHEN clause in your SELECT to return different columns based on the day. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 13, 2018 at 10:41
Hello, if it is possible, could you give example or correct above code please? I tried many different methods but I couldnt find easy way. — Hakan
– Hakan, Commented Sep 13, 2018 at 10:46
I can't because you didn't explain what you want to do. No table schema, no expected results. Do you have only one table? Why does the title say from another table then? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 13, 2018 at 10:51

armen · Accepted Answer · 2018-09-13 11:46:08Z

1

DECLARE @SQLText NVARCHAR(MAX) = ''
SELECT  @SQLText += 'SELECT (SELECT '
SELECT  @SQLText += ( 'TPR' +  Convert(varchar,DATEPART(day,a.DATE_TIME) )
    ) +
    ' from SHIFT_TABLE as s Where s.id = a.id ) from MAIN_TABLE as a where a.id=''1'' and a.ay=''12'' and a.yil=''2018''  '
FROM [YourTableWhere_DATE_TIME_Exists] AS a
print @SQLText

answered Sep 13, 2018 at 11:46

armen

1,2754 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select column name dynamically based on rows from another table in SQL server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related