ssms Sql 如何將同一組的data顯示出來

ssms sql

nepenthe1919 2023-07-01 11:35:41 ‧ 1366 瀏覽

分享至

目前我的資料是同一組data ,會有多個不同的value ,
如果直接用group by , 只會保留其中一筆value

需求是：
需要顯示一個data裡面有哪一些value
（出現了2次以上要怎麽處理呢（我需要要顯示 number1,number2,number3.....）？

想要的結果

我的做法
with com as ( Select
'A' Id, '1' data union all
Select 'B' Id, '1' data union all
Select 'E' Id, '1' data union all
Select 'G' Id, '1' data union all
Select 'C' Id, '2' data union all
Select 'D' Id, '2' data union all
Select 'F' Id, '2' data union all
),
com2 as ( Select A.id Aid, B.id Bid, B.data From com A Inner Join com B On A.data = B.data And A.Id <> B.Id )
Select data, min(Aid) number1, max(Bid) number2 From com2 Group by data

上面的sql 我用了Min,max 會漏掉資料（出現了3資料，沒有計算），所以是錯的

請救救菜鳥工程師

PPTaiwan iT邦好手 1 級 ‧ 2023-07-02 12:27:07 檢舉

COPY 你的問題直接丟到 GPT 也可以得到你要的解答

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 8 級 ‧ 2023-07-01 12:21:59

最佳解答

SQL 2022

with com as ( 
Select 'A' Id, '1' data union all
Select 'B' Id, '1' data union all
Select 'E' Id, '1' data union all
Select 'G' Id, '1' data union all
Select 'C' Id, '2' data union all
Select 'D' Id, '2' data union all
Select 'F' Id, '2' data 
)
  
select data,STRING_AGG (Id,',') WITHIN GROUP (ORDER BY data,Id) as Id_All
from com
group by data

Demo

SQL 2014

with com as ( 
Select 'A' Id, '1' data union all
Select 'B' Id, '1' data union all
Select 'E' Id, '1' data union all
Select 'G' Id, '1' data union all
Select 'C' Id, '2' data union all
Select 'D' Id, '2' data union all
Select 'F' Id, '2' data 
)

select data,left(Id_All,len(Id_All)-1) as Id_All
from ( 
SELECT distinct X.data,(SELECT cast(M.Id AS NVARCHAR ) + ',' from com as M
where M.data = X.data
FOR XML PATH('')) as Id_All
from com as X
) as G
order by data

Demo