select rows with same id and some other value in another column for that id

Question

select rows with same id and some other value in another column for that id

PJ_SQL

SSCertifiable

Points: 6703
More actions
September 15, 2022 at 10:08 pm

#4088315

Hello!
Example
If both rows have same ID and has Type ='Alias' then output should be as below:
+----------+-------+-------+
| ID |Name | Type |
+----------+-------+-------
| 1 | abc |Alias |
| 1 | ef | AA |
| 2 | AA | AB |
+----------+-------+--------
Need Ouput:
+----------+-------+-------+
| ID | Name | Alias |
+----------+-------+-------+
| 1 | abc | ef |
| 2 | AA | AB |
+----------+-------+--------

Thanks in advance.

September 19, 2022 at 9:35 am

This was removed by the editor as SPAM
September 28, 2022 at 9:28 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

You could divide the rows using two queries: one above and one below UNION ALL. Above SELECT ID's with [type]='Alias' and below SELECT ID's without any 'Alias'. You could assign the new alias using OUTER APPLY SELECT TOP(1) and without ORDER BY to randomly select names

with data_cte as (
    select *
    from (values (1, 'abc', 'Alias'), 
                 (1, 'ef', 'AA'),
                 (2, 'AA', 'AB')) v(id, [name], [type]))
select d.id, d.[name], oa.[name] Alias
from data_cte d
     outer apply (select top(1) dd.[name]
                  from data_cte dd
                  where dd.id=d.id
                        and dd.[type]<>'Alias') oa([name])
where d.[type]='Alias'
union all
select *
from data_cte d
where not exists (select 1
                  from data_cte dd
                  where dd.id=d.id
                        and dd.[type]='Alias');

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

DesNorton SSC-Insane Points: 24387 More actions · Answer 2

Shezsa wrote:

That is battling. I was stunned by your affiliation. I'm happy to see a particularly subject. Altruisticly go to my blog and read it.

Spam incoming .....

How to post data/code on a forum to get the best help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

DesNorton wrote:

Shezsa wrote:
That is battling. I was stunned by your affiliation. I'm happy to see a particularly subject. Altruisticly go to my blog and read it.
Spam incoming .....

Someone is running some really silly AI SPAM generators. They need to get their money back. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)