0

Please login or register to vote for this query.

(click on this box to dismiss)

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;

fork query

edited mar 20 18 anonymous

created jun 8 16 bluefeet