Combine multiple row to single row for unique values in multiple column

Question

I am trying to get unique rows based on unique values on multiple columns in SQL Server 2012.

Source Data:

UserId ---- P1 ---- P2 ------ P3

101 ------- NO ---- NO ------- YES

101 ------- NO ---- YES ------ NO

101 ------- YES ---- NO ------- NO

102 -------- NO ----- NO ------- NO

My source data has 'YES' in single columns for multiple rows.

I'm trying to get the result as follows:

Desired Output:

UserId ---- P1 ---- P2 ------ P3

101 ------- YES ---- YES ----- YES

102 -------- NO ----- NO ------- NO

Any ideas or code-sample will be greatly appreciated.

So YES also takes precedence over NO? Thus if you had R1-1 = NO NO NO and R1-2 = YES NO NO then you would be YES NO NO? Are there any other values of which to be aware? — PatrickSJ
– PatrickSJ, Commented Dec 5, 2017 at 6:50
Just YES and NO alone. Need to combine with UserId, (R1, R2, R3,...) based on single row for UserId — Muruga
– Muruga, Commented Dec 5, 2017 at 6:54
if none of the answer is correct then we need more explanation or few more example ?what id 102 had one yes ? — KumarHarsh
– KumarHarsh, Commented Dec 5, 2017 at 8:28

gofr1 · Accepted Answer · 2017-12-05 07:06:37Z

1

In SQL Server 2012 and higher you can use this construction:

SELECT  UserId,
        IIF(SUM(IIF(P1 = 'YES',1,0)) > 0,'YES','NO') as P1,
        IIF(SUM(IIF(P2 = 'YES',1,0)) > 0,'YES','NO') as P2,
        IIF(SUM(IIF(P3 = 'YES',1,0)) > 0,'YES','NO') as P3
FROM YourTable
GROUP BY UserId

Or, instead of IIF use CASE (SQL Server 2008 or higher):

SELECT  UserId,
        CASE WHEN SUM(CASE WHEN P1 = 'YES' THEN 1 ELSE 0 END) > 0 THEN 'YES' ELSE 'NO' END as P1,
        CASE WHEN SUM(CASE WHEN P2 = 'YES' THEN 1 ELSE 0 END) > 0 THEN 'YES' ELSE 'NO' END as P2,
        CASE WHEN SUM(CASE WHEN P3 = 'YES' THEN 1 ELSE 0 END) > 0 THEN 'YES' ELSE 'NO' END as P3
FROM YourTable
GROUP BY UserId

Output:

UserId  P1  P2  P3
101     YES YES YES
102     NO  NO  NO

answered Dec 5, 2017 at 7:06

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gotqn · Accepted Answer · 2017-12-05 07:11:02Z

1

In this case (handling only YES and NO values) simple MIN/MAX can solve the task.

DECLARE @SourceData TABLE
(
    [UserId] INT
   ,[P1] VARCHAR(3)
   ,[P2] VARCHAR(3)
   ,[P3] VARCHAR(3)
);

INSERT INTO @SourceData ([UserId], [P1], [P2], [P3])
VALUES ('101', 'NO', 'NO', 'YES')
      ,('101', 'NO', 'YES', 'NO')
      ,('101', 'YES', 'NO', 'NO')
      ,('102', 'NO', 'NO', 'NO');

SELECT [UserId]
      ,MAX([P1])
      ,MAX([P2])
      ,MAX([P3])
FROM @SourceData
GROUP By [UserId];

answered Dec 5, 2017 at 7:11

gotqn

44k47 gold badges167 silver badges257 bronze badges

Comments

Jayasurya Satheesh · Accepted Answer · 2017-12-05 06:57:32Z

0

Try this

WITH CTE
AS
(
   SELECT
      DISTINCT UserId
  FROM T1
)
SELECT
UserId,
P1 = CASE WHEN EXISTS(SELECT 1 FROM T1 WHERE UserId = CTE.UserId AND P1='YES')
THEN 'YES' ELSE 'NO' END,
P2 = CASE WHEN EXISTS(SELECT 1 FROM T1 WHERE UserId = CTE.UserId AND P2='YES')
THEN 'YES' ELSE 'NO' END,
P3  = CASE WHEN EXISTS(SELECT 1 FROM T1 WHERE UserId = CTE.UserId AND P3='YES')
THEN 'YES' ELSE 'NO' END
FROM CTE

Note : Assuming your table name as T1

answered Dec 5, 2017 at 6:57

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Collectives™ on Stack Overflow

Combine multiple row to single row for unique values in multiple column

Source Data:

Desired Output:

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Source Data:

Desired Output:

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related