Im new to Teradata and need to write a dynamic crosstab query.
I guess that first i need to create a temp table with the needed columns and then create a dynamic query that concatenates each column in the temp table to a case statement..
Im not sure this is the right way, any help on how to write a pivot query without hardcoding column names will be appriciated!
Did anyone ever wrote a pivot query without hard coding the columns ?
Is it possible to dynamically create a query without hardcoding the column names?
Yes, it is possible and done in the past.
SP will have the problem that they can't execute Selects as dynamic SQLs - at least in the past. Didn't check this for a long time.
So the other option is to use BTEQ for that. Export the dynamically generated SQL into a file and call it afterwards via a run statement.
But what do you want to do with the dynamic number of result columns. How do you handle this?
I have a specific TestNames and their results and I need to present the data in this format:
Date, TestOrigin, T1, T2, ....
1.2 US T1 Result T2 result
I did it with CASE statements and hardcoded the test names but i want to avoid harcoding.
Could you please post code for using cursors for dynamically constract a concatanation of the test names or the concatanations of CASE statements.
I know that cursors scalability is bad in TD but I guess in the case of 20 more or less test names it wont be a problem. Isnt it?
Im not familiar with BTEQ, how do I use it ?