Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the SQL out put in the below format. Need to convert them into the rows for each id, Based on the type the column name changes and store them in a QVD.
Id, First date , Last date, type
1, 01/Apr/2014, 04/Apr/2014, S
1,01/May/2014, 05/May/2014,B
1,10/May/2014,05/Jun/2014,C
2, 02/Apr/2014, 04/Apr/2014, S
2,03/May/2014, 05/May/2014,B
2,15/May/2014,05/Jun/2014,C
3, 01/Apr/2014, 04/Apr/2014, S
3,01/May/2014, 05/May/2014,B
3,10/May/2014,05/Jun/2014,C
....
ID | S_first_date | S_last_date | B_first_date | B_last_date | C_first_date | C_last_date |
1 | 1-Apr-14 | 4-Apr-14 | 1-May-14 | 5-May-14 | 10-May-14 | 5-Jun-14 |
1 | 2-Apr-14 | 4-Apr-14 | 3-May-14 | 5-May-14 | 15-May-14 | 5-Jun-14 |
3 | 1-Apr-14 | 4-Apr-14 | 1-May-14 | 5-May-14 | 10-May-14 | 5-Jun-14 |
thanks..
JJ
X:
Load * INLINE [
Id, First date , Last date, type
1,01/Apr/2014, 04/Apr/2014, S
1,01/May/2014, 05/May/2014,B
1,10/May/2014,05/Jun/2014,C
2,02/Apr/2014, 04/Apr/2014, S
2,03/May/2014, 05/May/2014,B
2,15/May/2014,05/Jun/2014,C
3,01/Apr/2014, 04/Apr/2014, S
3,01/May/2014, 05/May/2014,B
3,10/May/2014,05/Jun/2014,C];
Data:
LOAD
Id ,
Date(Sum(If(type='S',Date#([First date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS S_first_date,
Date(Sum(If(type='S',Date#([Last date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS S_last_date,
Date(Sum(If(type='B',Date#([First date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS B_first_date,
Date(Sum(If(type='B',Date#([Last date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS B_last_date,
Date(Sum(If(type='C',Date#([First date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS C_first_date,
Date(Sum(If(type='C',Date#([Last date], 'DD/MMM/YYYY'))),'D-MMM-YYYY') AS C_last_date
Resident X
Group By Id ;
STORE Data into Data.qvd(qvd);
DROP TABLE X;