Tsql union query

问题:

I’m looking for an efficient way to query a table. The table structure is:

CREATE TABLE [dbo].[CaseManager](
    [CaseID] [int] IDENTITY(1,1) NOT NULL,
    [SystemUserCreatedBy] [int] NULL,
    [SystemUserAssignee] [int] NULL,
     CONSTRAINT [PK_Case] PRIMARY KEY CLUSTERED 
(
    [CaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] 

The query should return for every caseID and userid (userid can be either SystemUserCreatedBy or SystemUserAssignee) bit columns that show if the use is Createdby or Assignee I managed to write it like this:

select  CaseID,UserID,
       max(CaseUser.IsAssignee) as IsAssignee,
       max(CaseUser.IsCreator) as IsCreator


  FROM
  (
  select CMassignee.CaseID,
         CMassignee.SystemUserAssignee as UserID,
         1 as IsAssignee ,
         0 as IsCreator 
      from CaseManager CMassignee 
  where  CMassignee.SystemUserAssignee is not null
  UNION
   select CMCreator.CaseID,
          CMCreator.SystemUserCreatedBy as UserID,
          0 as IsAssignee ,
          1 as IsCreator 
      from CaseManager CMCreator
  where  CMCreator.SystemUserCreatedBy is not null
    ) CaseUser
  group by CaseID,UserID

I’m pretty sure there is a better way to write it with scanning that table once .In that example I show only two columns (SystemUserCreatedBy as SystemUserAssignee) but actually I have five that need to be added.

Please see example of data:

SET IDENTITY_INSERT dbo.casemanager ON;
insert into casemanager(caseid,SystemUserCreatedBy,SystemUserAssignee)
values
(1,2222,3333)
SET IDENTITY_INSERT dbo.casemanager OFF;

In that case I’m looking to get :

CaseID      UserID      IsAssignee  IsCreator
----------- ----------- ----------- -----------
1           2222        0           1
1           3333        1           0

(2 row(s) affected)

回答1:

The CaseID is the Primary Key, so there's no need for aggregation (if only one of both columns is NOT NULL):

SELECT
   CaseID,
   COALESCE(SystemUserAssignee, SystemUserCreatedBy) AS UserID,
   CASE WHEN SystemUserAssignee IS NOT NULL THEN 1 ELSE 0 end AS IsAssignee,
   CASE WHEN SystemUserCreatedBy IS NOT NULL THEN 1 ELSE 0 end AS IsCreator
FROM CaseManager CMassignee 

Edit:

Based on the latest comments both columns can have data and it might be the same user, so your original query is ok (even if it scans the table twice), the only thing you need to change is UNION ALL instead of UNION.

But for 5 userid @Amit's answer should be the best.

回答2:

Perhaps using CASE statements

SELECT CaseID,
       CASE
         WHEN SystemUserAssignee IS NOT NULL THEN SystemUserAssignee
         ELSE SystemUserCreatedBy
       END as UserID,
       CASE
         WHEN SystemUserAssignee IS NOT NULL THEN 1
         ELSE 0
       END as IsAssignee,
       CASE
         WHEN SystemUserAssignee IS NOT NULL THEN 0
         ELSE 1
       END as IsCreator
FROM CaseManager
WHERE SystemUserAssignee IS NOT NULL OR SystemUserCreateBy IS NOT NULL

回答3:

SELECT  CaseID ,
        CASE WHEN SystemUserAssignee IS NOT NULL THEN SystemUserAssignee
             ELSE SystemUserCreatedBy
        END AS UserID ,
        CASE WHEN SystemUserAssignee IS NOT NULL THEN 1
             ELSE 0
        END AS IsAssignee ,
        CASE WHEN SystemUserAssignee IS NOT NULL THEN 0
             ELSE 1
        END AS IsAssignee
FROM    CaseManager; 

回答4:

It can be achieved by joining a "static table" to duplicate each row the necessary amount of times (2 in this sample, but can be expanded to any number).

SELECT CaseID, UserID, MAX(IsCreator) IsCreator, MAX(IsAssignee) IsAssignee
FROM (


  SELECT CaseID, CASE
    WHEN Switcher = 1 THEN SystemUserCreatedBy
    WHEN Switcher = 2 THEN SystemUserAssignee
    END AS UserID,
    IIF(Switcher = 1, 1, 0) AS IsCreator,
    IIF(Switcher = 2, 1, 0) AS IsAssignee
  FROM dbo.CaseManager CROSS JOIN (VALUES(1),(2)) switcher(Switcher)
  WHERE (SystemUserCreatedBy IS NOT NULL AND Switcher = 1) OR
    (SystemUserAssignee IS NOT NULL AND Switcher = 2)


) sub
GROUP BY CaseID, UserId
ORDER BY CaseID, UserId

The idea is that the switcher table has a row for each of the flags, and then the main SELECT query uses this value to pick the right values for each row.

EDIT: As rightfully mentioned by dnoeth, I had to wrap the query with a grouping to match OP. I thought this was distracting from the main idea of the answer and so this part of the solution is "separated" by empty lines.

Also available: SQLFiddle

*Note: The VALUES(1),(2) syntax is only available in SQL SERVER 2008 +

EDIT 2: I've had a second thought about the solution, and came up with a more elegant query. I'm not sure which version will turn up quicker, and I believe the original version could be useful in other scenarios so I'm keeping both.

Version 2:

SELECT CaseID, UserID, IsCreator=IIF(UserID=SystemUserCreatedBy, 1, 0),
 IsAssignee=IIF(UserID=SystemUserAssignee, 1, 0)
FROM (
  SELECT DISTINCT *, CASE
    WHEN Switcher = 1 THEN SystemUserCreatedBy
    WHEN Switcher = 2 THEN SystemUserAssignee
    END AS UserID
  FROM dbo.CaseManager CROSS JOIN (VALUES(1),(2)) switcher(Switcher)
) sub
WHERE UserID IS NOT NULL
ORDER BY CaseID, UserId

And the updated SQLFiddle