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