convert rows to columns based on ID and Type

[#1]

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

[sasiparupudi1]

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;