Select top 1 column along with CSV column

Question

Hi All, I've a table as below:
TableId SId Data DataOwner
1 10 aaaa 1001
2 10 aaaa 1000
3 20 bbbb 1000
4 10 ccc 2000

Declare @table1 table(TableId int, SId int, Data varchar(200), DataOwner int)
insert into @table1 values(1, 10, 'aaaa', 1001)
,(2, 10, 'aaaa', 1000)
,(3, 20, 'bbbb', 1000)
,(4, 10, 'ccc', 2000)

When data is same for SId column then select dataowner values in comma separated in ascending order and take any one of the TableId
TableId SId Data DataOwner
1 10 aaaa 1000, 1001
3 20 bbbb 1000
4 10 ccc 2000

TIA

Answer 1

Please try the following T-SQL:

-- DDL and sample data population, start
DECLARE @tbl table(TableId int, SId int, Data varchar(200), DataOwner int)
insert into @tbl VALUES
(1, 10, 'aaaa', 1001)
,(2, 10, 'aaaa', 1000)
,(3, 20, 'bbbb', 1000)
,(4, 10, 'ccc', 2000);
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH rs AS
(
SELECT c.TableId, c.SId, c.Data
   , STUFF((SELECT @separator + CAST(DataOwner AS VARCHAR(30)) AS [text()]
        FROM @tbl AS O
        WHERE O.Data = C.Data 
        ORDER BY SId, DataOwner ASC
        FOR XML PATH('')), 1, 1, NULL) AS DataOwnerList
, ROW_NUMBER() OVER (PARTITION BY Sid, data ORDER BY C.TableId) AS seq
FROM @tbl AS C
)
SELECT TableId, SId, Data, DataOwnerList
FROM rs
WHERE seq = 1;

Answer 2

Hope it helps:

;WITH CTE1 AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY SId, Data ORDER BY TableId) AS Ranked
    FROM @table1
),
CTE2 AS (
    SELECT 
        t.SId, 
        t.Data,
        STUFF(
            (
                SELECT ',' + CAST(DataOwner AS varchar(20)) 
                FROM @table1 
                WHERE SId = t.SId AND Data = t.Data
                ORDER BY DataOwner
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,
            1,
            ''
        ) AS DataOwner
    FROM @table1 AS t
    GROUP BY t.SId, t.Data
)

SELECT c1.TableId, c1.SId, c1.Data, c2.DataOwner
FROM CTE1 AS c1
INNER JOIN CTE2 AS c2 ON c1.SId = c2.SId AND c1.Data = c2.Data
WHERE c1.Ranked = 1
ORDER BY c1.TableId;

Answer 3

Declare @table1
 table(TableId int, SId int, Data varchar(200), DataOwner int)
insert into @table1 values(1, 10, 'aaaa', 1001)
,(2, 10, 'aaaa', 1000)
,(3, 20, 'bbbb', 1000)
,(4, 10, 'ccc', 2000)

--SQL Server 2017 (14.x) and later 
select min(TableId) TableId
, SID,Data
, string_agg(DataOwner,',') WITHIN GROUP(order by SId ) DataOwner
from @table1
GROUP BY  SID,Data

Answer 4

 --SQL Server 2016 or later

Declare @table1
  table(TableId int, SId int, Data varchar(200), DataOwner int)
 insert into @table1 values
  (1, 10, 'aaaa', 1001)
 ,(2, 10, 'aaaa', 1000)
 ,(3, 20, 'bbbb', 1000)
 ,(4, 10, 'ccc', 2000)

;with mycte as (
select Distinct SId , Data, DataOwner
, min(TableId) over(partition by SId , Data) TableId
from  @table1

)

,mycte2 as (
SELECT t1.TableId, SId , Data,  (SELECT  Vals =  JSON_QUERY(
(
SELECT Cast(DataOwner as varchar(30)) DataOwner
FROM mycte t2 WHERE t2.SId = t1.SId and t2.Data = t1.Data
FOR JSON PATH )
)    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)   jsCol
FROM mycte t1
Group by SId , Data,TableId)


select TableId, SId , Data, 
Concat(JSON_VALUE (jsCol,'$.Vals[0].DataOwner'),
','+JSON_VALUE (jsCol,'$.Vals[1].DataOwner'),
','+JSON_VALUE (jsCol,'$.Vals[2].DataOwner'),
','+JSON_VALUE (jsCol,'$.Vals[3].DataOwner') )  DataOwner

from mycte2
order by  TableId

Answer 5

Hi Bluecloud1921-1222,

For versions before SQL Server 2017, you can refer to the following code：

with cte1  
 as   
  (select *,row_number() over(partition by Data order by Data) rn,LEAD (DataOwner ,1)     
    over (partition by Data order by Data ) DataOwner1 from table1)  
   ,cte2 as  
   (select *, cast(DataOwner1 as varchar)+','+cast(DataOwner as varchar) tsum  
    from cte1)  
 ,cte3 as  
 (select *,case when DataOwner1 is not null then tsum else cast(DataOwner as varchar) end DataOwner2  
    from cte2)   
select TableId,SId,Data,DataOwner2 as DataOwner from cte3  
where rn<2  

SQL Server 2017 added the string_agg function, which will be very convenient to use：

with cte1  
  as (select Data,string_agg(DataOwner,',') within group(order by DataOwner) as DataOwner  
      from table1  
      group by Data)  
   ,cte2 as  
     (select t.TableId,t.SId,c1.Data,c1.DataOwner from table1 t  
      join cte1 c1  
      on t.Data=c1.Data)  
    ,cte3 as  
   (select *, row_number() over(partition by Data order by Data) rn from cte2)  
  
select TableId,SId,Data,DataOwner from cte3  
where rn<2  

Please also refer below link for more details:STRING_AGG (Transact-SQL)
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo