Pritam Jyoti Ray Pritam Jyoti Ray - 3 years ago 82
SQL Question

Get a single row from multiple rows of a single table

I have a table as given below. Here some data also added.

CREATE TABLE [dbo].[ApplicationState](
[ID] [varchar](8) NULL,
[State] [int] NULL,
[subState] [int] NULL,
[SL] [int] NULL,
[CanView] [bit] NULL,
[CanEdit] [bit] NULL,
[CanSave] [bit] NULL,
[CanApproved] [bit] NULL,
[CanReject] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ApplicationState] ([ID], [State], [subState], [SL], [CanView], [CanEdit], [CanSave], [CanApproved], [CanReject]) VALUES (N'00000001', 5, 2, 1, 1, 0, 0, 0, 0)
INSERT [dbo].[ApplicationState] ([ID], [State], [subState], [SL], [CanView], [CanEdit], [CanSave], [CanApproved], [CanReject]) VALUES (N'00000001', 5, 3, 1, 1, 0, 1, 0, 0)
INSERT [dbo].[ApplicationState] ([ID], [State], [subState], [SL], [CanView], [CanEdit], [CanSave], [CanApproved], [CanReject]) VALUES (N'00000001', 5, 4, 2, 1, 0, 0, 1, 1)
INSERT [dbo].[ApplicationState] ([ID], [State], [subState], [SL], [CanView], [CanEdit], [CanSave], [CanApproved], [CanReject]) VALUES (N'00000001', 5, 2, 2, 1, 1, 0, 0, 0)


Need a optimized query (only show one row) to get below result.

ID | State | CanView | CanEdit | CanSave | CanApproved | CanReject

1 | 5 | 1 | 1 | 1 | 1 | 1

Answer Source

Based on your comments, it appears this is what you are looking for. You will need to better clarify your requirements if this does not meet the need.

Edit: If your goal is to group by ID and State, as Ivan mentioned, then do this:

SELECT
    ID,
    State,
    MAX(CAST(CanView AS INT)) AS CanView,
    MAX(CAST(CanEdit AS INT)) AS CanEdit,
    MAX(CAST(CanSave AS INT)) AS CanSave,
    MAX(CAST(CanApproved AS INT)) AS CanApproved,
    MAX(CAST(CanReject AS INT)) AS CanReject
FROM
    dbo.ApplicationState
GROUP BY
    ID,
    State;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download