Dynamically deside on number of columns in a recursive query

[vijaykumardasar]

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...

WB	BFOR_SEQ	BFOR_VER_SEQ	AFTR_SEQ	AFTR_VER_SEQ	COL_NAME	BFOR_VALUE	AFTR_VALUE
AA	1	1	1	2	BILL_DT	12/31/2017	1/31/2018
AA	1	1	1	2	BILL_CUST	ABC	BCD
AA	1	2	1	3	PYMT_DT	1/20/2018	2/10/2018
AA	1	2	1	3	PYMT_CUST	EDF	GHI
AA	1	3	1	4	NET_WGT	1000	9000
BB	1	1	1	2	SERV_DT	1/20/2018	1/31/2018
BB	1	1	1	2	CUST_NM	BOB	GEORGE
BB	1	2	2	1	SERV_DT	12/31/2017	1/31/2018
BB	1	2	2	1	CUST_NM	GEORGE	BOB
BB	2	1	2	2	BILL_DT	12/31/2017	1/31/2018
BB	2	1	2	2	BILL_CUST	ABC	BCD
BB	2	2	2	3	PYMT_DT	1/20/2018	2/10/2018
BB	2	2	2	3	PYMT_CUST	EDF	GHI

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

WB

BFOR_SEQ

BFOR_VER_SEQ

AFTR_SEQ

AFTR_VER_SEQ

BFOR_BILL_DT

AFTR_BILL_DT

BFOR_BILL_CUST

AFTR_BILL_CUST

BFOR_PYMT_DT

AFTR_PYMT_DT

BFOR_PYMT_CUST

AFTR_PYMT_CUST

BFOR_NET_WGT

AFTR_NET_WGT

BFOR_SERV_DT

AFTR_SERV_DT

BFOR_CUST_NM

AFTR_CUST_NM

AA

1

1

1

2

12/31/2017

1/31/2018

ABC

BCD

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

AA

1

2

1

3

NULL

NULL

NULL

NULL

1/20/2018

2/10/2018

EDF

GHI

NULL

NULL

NULL

NULL

NULL

NULL

AA

1

3

1

4

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

1000

9000

NULL

NULL

NULL

NULL

BB

1

1

1

2

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

1/20/2018

1/31/2018

BOB

GEORGE

BB

1

2

2

1

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

12/31/2017

1/31/2018

GEORGE

BOB

BB

2

1

2

2

12/31/2017

1/31/2018

ABC

BCD

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

BB

2

2

2

3

NULL

NULL

NULL

NULL

1/20/2018

2/10/2018

EDF

GHI

NULL

NULL

NULL

NULL

NULL

NULL

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

[dnoeth]

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.

[GJColeman]

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

[vijaykumardasar]

Hi Dnoeth,

Thank you for the response. I tried PIVOT function and it serves my need partially. There are 365 values for col_name. I don't want to list all these columns when I query for a particular WB number. I want to see only the columns for which there is a record in the source table. If i am querying for more than one WB number, I would like to see only the columns that these two WBs have records for. in other words, the number of the columns in the resultset is dynamic. Any work around using Pivot? Or I need a SP? please let me know if it is not clear. 

Thanks

Vijay

[dnoeth]

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.