Pivot query with dynamic number of coumns

Database
Enthusiast

Pivot query with dynamic number of coumns

Hi,

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!

Thx, Anna

3 REPLIES
Enthusiast

Re: Pivot query with dynamic number of coumns

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?

Thanks,

anna

Supporter

Re: Pivot query with dynamic number of coumns

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?

Enthusiast

Re: Pivot query with dynamic number of coumns

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 ?

Thx alot!!!

Anna