SQL Server Choose between two columns based on aggregate of another column

yzckvree 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(106)

If PEOPLE are the same as in other rows but COMPANY is different, then give me SERVICE_1 ; if PEOPLE are the same like other rows and COMPANY are also the same then give me SERVICE2 . How can I write code for this in T-SQL? Can it be achieved using the row_number clause?

Original data:
| PEOPLE | COMPANY | SERVICE_1 | SERVICE_2 |
| ------------ | ------------ | ------------ | ------------ |
| KRISH | AA | HYDRO | WATER |
| KRISH | BB | NULL | WATER |
| JOHN | CC | NULL | ROAD |
| JOHN | CC | NULL | ELECY |
| JOHN | CC | NULL | GAS |

Desired results:

PEOPLE	COMPANY	SERVICE
KRISH	AA	HYDRO
KRISH	BB	NULL
JOHN	CC	ROAD
JOHN	CC	ELECY
JOHN	CC	GAS

sql-server

来源：https://stackoverflow.com/questions/76748792/choose-between-two-columns-based-on-aggregate-of-another-column

1条答案

按热度按时间

pbpqsu0x1#

Probably a dozen ways to do this. One is you can count the distinct companies separately and then join, exposing the service with a CASE expression ( example ):

WITH cte AS 
(
  SELECT PEOPLE, c = COUNT(DISTINCT COMPANY)
  FROM dbo.Something GROUP BY PEOPLE
)
SELECT cte.PEOPLE, s.COMPANY,
  SERVICE = CASE WHEN cte.c = 1 THEN s.SERVICE_2 ELSE s.SERVICE_1 END
FROM cte JOIN dbo.Something AS s
ON cte.PEOPLE = s.PEOPLE;

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Choose between two columns based on aggregate of another column

1条答案

相关问题

热门标签

最新问答