Dynamically deside on number of columns in a recursive query

Database
Enthusiast

Dynamically deside on number of columns in a recursive query

Hi All,

 

I have a situation here which I don't know how to handle. Below is what is have and what I am looking for...

Input...

WBBFOR_SEQBFOR_VER_SEQAFTR_SEQAFTR_VER_SEQCOL_NAMEBFOR_VALUEAFTR_VALUE
AA1112BILL_DT12/31/20171/31/2018
AA1112BILL_CUSTABCBCD
AA1213PYMT_DT1/20/20182/10/2018
AA1213PYMT_CUSTEDFGHI
AA1314NET_WGT10009000
BB1112SERV_DT1/20/20181/31/2018
BB1112CUST_NMBOBGEORGE
BB1221SERV_DT12/31/20171/31/2018
BB1221CUST_NMGEORGEBOB
BB2122BILL_DT12/31/20171/31/2018
BB2122BILL_CUSTABCBCD
BB2223PYMT_DT1/20/20182/10/2018
BB2223PYMT_CUSTEDFGHI

 

 

I need help in writing a SQL to present output as below

WBBFOR_SEQBFOR_VER_SEQAFTR_SEQAFTR_VER_SEQBFOR_BILL_DTAFTR_BILL_DTBFOR_BILL_CUSTAFTR_BILL_CUSTBFOR_PYMT_DTAFTR_PYMT_DTBFOR_PYMT_CUSTAFTR_PYMT_CUSTBFOR_NET_WGTAFTR_NET_WGTBFOR_SERV_DTAFTR_SERV_DTBFOR_CUST_NMAFTR_CUST_NM
AA111212/31/20171/31/2018ABCBCDNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
AA1213NULLNULLNULLNULL1/20/20182/10/2018EDFGHINULLNULLNULLNULLNULLNULL
AA1314NULLNULLNULLNULLNULLNULLNULLNULL10009000NULLNULLNULLNULL
BB1112NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL1/20/20181/31/2018BOBGEORGE
BB1221NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL12/31/20171/31/2018GEORGEBOB
BB212212/31/20171/31/2018ABCBCDNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
BB2223NULLNULLNULLNULL1/20/20182/10/2018EDFGHINULLNULLNULLNULLNULLNULL

 

The number of record, versions, columns per a WB are not fixed. I need suggesion about how I can write a SQL to present in the above format.

 

I appriciate any help in this regards.

 

Thanks

Vijay

4 REPLIES
Junior Contributor

Re: Dynamically deside on number of columns in a recursive query

Why recursive?

This is a pivot query (either old style using max(case) or using the PIVOT function in TD16+).

When the COL_NAME values are not know in advance you need a Stored Procedure to create it dynamically.

Teradata Employee

Re: Dynamically deside on number of columns in a recursive query

Is this what you need?

 

select WB, BFOR_SEQ, BFOR_VER_SEQ, AFTR_SEQ, AFTR_VER_SEQ,
    MAX(case BFOR_BILL_DT when COL_NAME = 'BILL_DT' then BFOR_BILL_DT end)
     as "BFOR_BILL_DT"
group by 1,2,3,4,5
order by 1,2,3,4,5

Enthusiast

Re: Dynamically deside on number of columns in a recursive query

Hi 

 

 

 

Junior Contributor

Re: Dynamically deside on number of columns in a recursive query

Whenever the number of values/columns is not known you must use Dynamic SQL.

In a SP you can select the COL_NAMEs and create a matching PIVOT.

 

Or the client application does the PIVOT.