sql - Generate the columns dynamically based on the date range -

sql - Generate the columns dynamically based on the date range -

i have table metric data

metric id metric date metric result metric calculation color -------------------------------------------------------------------------------------- 001 2004-04-01 1 greenish 002 2004-04-01 0 reddish 001 2004-05-01 1 greenish 002 2004-05-01 5 yellowish 003 2004-05-01 2 greenish

i want result-set like

metric id apr result apr calc color may result may calc color --------------------------------------------------------------------------------------- 001 1 greenish 1 reddish 002 0 reddish 5 yellowish 003 2 greenish

i have used pivot table concept giving me duplicate results like

metric id apr result apr calc color may result may calc color --------------------------------------------------------------------------------------- 001 1 greenish null null 001 null null 1 reddish 002 0 reddish null null 002 null null 5 yellowish 003 2 greenish

this query used

;with cte ( select * ( select c.metric_id,result,color metricdata ) q pivot (max(result) [result] in ([april],[may]) )pv ) pivot (max(color) [result] in ([april],[may]) )pv1 )

the metric result , calculation color column should generated based on date limit provided query.

thanks in advance.

try instead of pivot

select metricid, case when datepart(metric_date)=4 metric_result else o end 'april result', case when datepart(metric_date)=4 metric_color else null end 'april calc color', case when datepart(metric_date)=5 metric_result else o end 'mayresult', case when datepart(metric_date)=5 metric_color else null end 'may calc color' metric

sql sql-server