Q&A about the site for parents, grandparents, nannies and others with a parenting role
CREATE table #yourtable ([ID] int, [Status] varchar(10), [Effective Date] varchar(10)) ; INSERT INTO #yourtable ([ID], [Status], [Effective Date]) VALUES (2, 'Open', '01-Oct-07'), (2, 'In Work', '01-Oct-07'), (2, 'Closed', '02-Oct-07'), (3, '#1 Met', '03-Oct-07'), (3, 'Canceled', '04-Oct-07') ; SELECT * FROM ( SELECT [ID], [Status], [Effective Date] FROM #yourtable ) AS SourceTable PIVOT(max([Status]) FOR [Effective Date] IN([Open], [In Work], [Closed], [#1 Met], [Canceled])) AS PivotTable;