Consolidating Duplicate Rows into 1 Row with Multiple Columns

Question

I was wondering how I can take a data set that looks like this:

Table 1:

RecordID    Code
A   351
A   352
A   353
A   354

to look like this:

Table 2:

you can already assume I created a second table with the column headers I created below

RecordID    351 352 353 354 355 356
   A         Y   Y   Y   Y   N   N

Thank you in advance for your help.

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Dec 30, 2015 at 21:59

Tim Biegeleisen · Accepted Answer · 2015-12-30 22:08:54Z

1

SELECT RecordID,
    CASE WHEN SUM(CASE WHEN Code = 351 THEN 1 ELSE 0 END) = 1 THEN 'Y' ELSE 'N' AS [351],
    CASE WHEN SUM(CASE WHEN Code = 352 THEN 1 ELSE 0 END) = 1 THEN 'Y' ELSE 'N' AS [352],
    CASE WHEN SUM(CASE WHEN Code = 353 THEN 1 ELSE 0 END) = 1 THEN 'Y' ELSE 'N' AS [353],
    CASE WHEN SUM(CASE WHEN Code = 354 THEN 1 ELSE 0 END) = 1 THEN 'Y' ELSE 'N' AS [354],
    CASE WHEN SUM(CASE WHEN Code = 355 THEN 1 ELSE 0 END) = 1 THEN 'Y' ELSE 'N' AS [355],
    CASE WHEN SUM(CASE WHEN Code = 356 THEN 1 ELSE 0 END) = 1 THEN 'Y' ELSE 'N' AS [356]
FROM yourTable
GROUP BY RecordID

answered Dec 30, 2015 at 22:08

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

WillT2118 Over a year ago

Thanks guys! this makes sense and should work, ill give it a shot.

Iłya Bursov · Accepted Answer · 2015-12-30 22:07:13Z

0

you can try something like this:

select
    RecordID,
    iif(sum(iif([Code]=351,1,0))>0,'Y','N'),
    iif(sum(iif([Code]=352,1,0))>0,'Y','N'),
    iif(sum(iif([Code]=353,1,0))>0,'Y','N'),
    iif(sum(iif([Code]=354,1,0))>0,'Y','N'),
    iif(sum(iif([Code]=355,1,0))>0,'Y','N'),
    iif(sum(iif([Code]=356,1,0))>0,'Y','N')
from table
group by RecordID

answered Dec 30, 2015 at 22:07

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

5 Comments

Lock Over a year ago

That's an interesting way of doing it. Why not just use max(case when code = 351 then 'Y' else 'N' end)

Iłya Bursov Over a year ago

@Lock in this case we will depend on the order of ascii codes, what if we need to localize or use other symbols?

Lock Over a year ago

@Lashlane. Not sure I see what localization has to do with this? Don't get me wrong.. I'm not saying yours is in anway wrong.. just never seen it done like that and to me the intent isn't as clear. Can you explain how this affects the order of ascii codes?

Iłya Bursov Over a year ago

@Lock your approach works only because ascii code of Y > N, for example in russian it will be vice versa (Д (yes) < Н (no)), so for russian letters you need to use min

Lock Over a year ago

@Lashlane Ah I see what you mean now. I didn't full understand the question!

Collectives™ on Stack Overflow

Consolidating Duplicate Rows into 1 Row with Multiple Columns

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related