SQL Server: select from table with/without aggregate columns using groupby-CodePudding

I have the columns and rows for the table A,

tableid	featureid	col3	col4	coldatetime
1	1	AD	4	2022-06-22 09:00:00
2	2	BC	5	2022-06-22 09:00:00
3	1	AE	6	2022-06-22 10:00:00
4	3	BD	7	2022-06-22 11:00:00
5	2	BB	8	2022-06-22 16:00:00

I need the following result in the SQL Server,

featureid	col3	col4
1	AD	4,6
2	BC	5,8
3	BD	7

If I run the following query:

select featureid,
       STRING_AGG(col3,',') as col3,
       STRING_AGG(col4,',') as col4 
from table_server 
group by feature_id;

I am getting the following result,

featureid	col3	col4
1	AD,AE	4,6
2	BC,BD	5,8
3	BD	7

How Should I change my query to get the Col3 to have only one record?

I have this clarification, whether this is possible or not?

I have zero knowledge with SQL Server, can anyone help me?

CodePudding user response：

If you first transform the irrelevant "col3" values to NULL, you can use your own code, as long as the STRING_AGG function ignores them completely.

WITH cte AS (
    SELECT tableid,
           featureid,
           CASE WHEN col4 = MIN(col4) OVER(PARTITION BY featureid) 
                THEN col3 
           END AS col3,
           col4
    FROM table_server
)
select featureid,
       STRING_AGG(col3,',') as col3,
       STRING_AGG(col4,',') as col4 
from cte 
group by featureid;

Check the demo here.

CodePudding user response：

Seems like you need MIN instead of STRING_AGG

select
  s.featureid,
  MIN(s.col3, ',') as col3,
  STRING_AGG(s.col4, ',') as col4 
from table_server s
group by
  s.feature_id;