CODEWITHSUNDEEP
sqlsql-server
Pritam Jyoti Ray
Pritam Jyoti Ray

Reputation: 330

Get a single row from multiple rows of a single table

I have a table as given below. Here, some data is 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 an optimized query (only show one row) to get the below result: 

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

Upvotes: 0

Views: 83

Answers (2)

Ajay2707
Ajay2707

Reputation: 5808

Based on your input and require output, I think you want any value more than zero ,consider output 1.

SELECT
    ID,
    State,
    Case when  MAX(CanView) > 1 then 1 else 0 end AS CanView,
    Case when MAX(CanEdit) > then 1 else0 end AS CanEdit,
    MAX(CanSave) AS CanSave,
    MAX(CanApproved) AS CanApproved,
    MAX(CanReject) AS CanReject
FROM
    dbo.ApplicationState
GROUP BY
    ID,
    State;

Same you can do for other column

Upvotes: 0

Nick
Nick

Reputation: 7451

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;

Upvotes: 3

Related Questions