CODEWITHSUNDEEP
sqlsubqueryamazon-redshift
Mez
Mez

Reputation: 1

Concatenate IDs only IF not unique in table

I am bit stuck in writing a query that might be easy for some of you. I am working with Redshift in Coginiti. So this is the problem I want to solve: I have a a big table but for this particular query I will only use 3 columns: ID, X,Y the requirement is if ID is unique then I should leave it as is, ID. If ID is not unique then I want to concatenate ID,X,Y. I am not looking to overwrite the column but rather create a new column I would call NEW_ID

if ID is unique in table T-->ID else concatenate(ID,X,Y) using '_' as delimiter

I did have a kind of solution, where I write a subquery to get the count of ID then write and if statement saying if count(ID)=1 then ID, else the concatenated but I am blanking out on to actually implement it in SQL world.

Thanks, I appreciate your help in advance :)

I did have a kind of solution, where I write a subquery to get the count of ID then write and if statement saying if count(ID)=1 then ID, else the concatenated but I am blanking out on to actually implement it in SQL world.

SELECT *, CONCAT(ID,X,Y)
from table 
left join ....got stuck here on how to tie it to the next part 

SELECT ID, COUNT(ID)
FROM table 
group by id
having count(ID)<>1 ...or perhaps =1. I need to work with all values anyway

enter image description here

Upvotes: 0

Views: 73

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11092

This should be straight forward. On Redshift I like the decode() function for cases like this but a CASE statement works just as well.

select id, x, y, 
  decode(id_count, 1, id, id::text || '_' || x || '_' || y) as concat_col
from (
  select id, x, y, count(*) over (partition by id) as id_count
  from <table>
);

Upvotes: 1

Related Questions