查询列之间的差异

Question

我有这样的示例数据:

臭 中性臭

似乎你可以使用CONCAT_WS的组合(假设你在一个完全支持的版本的SQL Server，因为你没有说明其他)，CASE和COUNT(DISTINCT):

SELECT ID,
CONCAT_WS(', ', CASE WHEN COUNT(DISTINCT Colour) > 1 THEN 'Different Colours' END,
CASE WHEN COUNT(DISTINCT Scent) > 1 THEN 'Different Scents' END) AS Remarks
FROM(VALUES(1,'Red','Stinky'),
(1,'Yellow','Flower-like'),
(1,'Green','Neutral'),
(2,'Blue','Stinky'),
(2,'Blue','Flower-like'),
(2,'Blue','Flower-like'))V(ID, Colour, Scent)
GROUP BY ID;