I have this table:
ID Value
------------
1 car
1 moto
2 car
2 moto
3 moto
3 apple
4 gel
4 moto
5 NULL
note that moto is common to all IDs.
I would to obtain a single row with this result
car*, moto, apple*, gel*
i.e.
car, apple, gel with an asterisk because is present but NOT in all IDs moto without an asterisk because is COMMON to all IDs
If ID + Value are Unique
SELECT Value, CASE WHEN COUNT(*) <> (SELECT COUNT(DISTINCT ID) FROM MyTable) THEN '*' ELSE '' END AS Asterisk FROM MyTable WHERE Value IS NOT NULL GROUP BY Value
Note that this won't group in a single line. And note that your question is wrong. ID 5 is an ID, so moto isn't common to all the IDs. It's common to all the IDs that have at least a value.
If we filter these IDs as written,
SELECT Value, CASE WHEN COUNT(*) <> (SELECT COUNT(DISTINCT ID) FROM MyTable WHERE Value IS NOT NULL) THEN '*' ELSE '' END FROM MyTable WHERE Value IS NOT NULL GROUP BY Value
To "merge" the * with Value, simply replace the , with a +, like:
SELECT Value + CASE WHEN COUNT(*) <> (SELECT COUNT(DISTINCT ID) FROM MyTable WHERE Value IS NOT NULL) THEN '*' ELSE '' END Value FROM MyTable WHERE Value IS NOT NULL GROUP BY Value
To make a single line use one of https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ I'll add that, sadly, tsql doesn't have any native method to do it, and all the alternatives are a little ugly :-)
In general, the string aggregation part is quite common on SO (and outside of it) Concatenate row values T-SQL, tsql aggregate string for group by, Implode type function in SQL Server 2000?, How to return multiple values in one column (T-SQL)? and too many others to count :-)
