additional question:

The actual situation I have is like this:

StatusType STATUS Object date

---------- ---------- ---------- -----------------------

status1 Closed Obj1 2000-06-01 00:00:00.000

status1 Closed Obj1 2013-01-01 00:00:00.000

status1 Open Obj1 1998-01-01 00:00:00.000

status1 Open Obj1 2008-03-06 00:00:00.000

status2 ACTIVE Obj1 1999-01-01 00:00:00.000

status2 check Obj1 2014-05-01 00:00:00.000

status2 INACTIV Obj1 2000-08-29 00:00:00.000

status2 UNKNOWN Obj1 2004-05-06 00:00:00.000

... with more status types.

What I do now is I create separate CTE (or some kind of query/table) for every StatusType. I could of course pivot it as well.

But maybe there's better solution in this situation?

code for source table:

CREATE TABLE [dbo].[StatusTable](

[StatusType] [varchar](7) NOT NULL,

[STATUS] [varchar](10) NULL,

[Object] [varchar](10) NULL,

[date] [datetime] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status1', N'Closed', N'Obj1', CAST(0x00008F4400000000 AS DateTime))

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status1', N'Closed', N'Obj1', CAST(0x0000A13900000000 AS DateTime))

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status1', N'Open', N'Obj1', CAST(0x00008BD200000000 AS DateTime))

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status1', N'Open', N'Obj1', CAST(0x00009A5700000000 AS DateTime))

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status2', N'ACTIVE', N'Obj1', CAST(0x00008D3F00000000 AS DateTime))

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status2', N'check', N'Obj1', CAST(0x0000A31E00000000 AS DateTime))

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status2', N'INACTIV', N'Obj1', CAST(0x00008F9D00000000 AS DateTime))

INSERT [dbo].[StatusTable] ([StatusType], [STATUS], [Object], [date]) VALUES (N'status2', N'UNKNOWN', N'Obj1', CAST(0x000094DF00000000 AS DateTime))