检查：

sql server - 多行到一个以逗号分隔的值 - Stack Overflow [ ^ ]

将表中的行值转换为单个连接字符串 - SQLMatters [ ^ ]

你可以尝试这个

  DECLARE   @ txnid   int  
 DECLARE   @ table   table （Txnid  INT ，Items  VARCHAR （ 15 ））

 DECLARE  cur  CURSOR   FOR  
 SELECT   DISTINCT  trans.Txnid  FROM  trans 

 OPEN  cur 
 FETCH  NEXT  FROM  cur  INTO   @ txnid  

 WHILE   @@ FETCH_STATUS  = 0 
 BEGIN  

 INSERT   INTO   @ table  
 SELECT   @ txnid  Txnid，STUFF（（ SELECT  ' ，'  + items  FROM 项 WHERE  itemid  in （ SELECT  itemid  FROM  trans  WHERE  txnid = @ txnid） FOR  XML PATH（' '））， 1 ， 1 ，' '）项
 FETCH  NEXT  FROM  cur  INTO   @ txnid  

 END  
 SELECT  *  FROM   @ table  
 CLOSE  cur 
 DEALLOCATE  cur 

I have two tables in my sql database 1 called transaction and data in it is like in the following form

trans_ID        Item_ID
100               1
100               3
100               4
200               1
200               2
300               3

and another table called items which has the following attributes

ITem_ID         Item
1                I1
2                I2
3                I3
4                I4
5                I5

I want to write a query that gives an output of

Trans_ID       Items
100             I1,I3,I4
200             I1,I2
300              I3

Is this possible ?

What I have tried:

I tried a query like

select trans_id , concat(Items,',')
from transaction join items on transaction.Item_ID=items.Item_ID
group by trans_id

but it brings an error

Check this:
sql server - Multiple rows to one comma-separated value - Stack Overflow[^]
Converting row values in a table to a single concatenated string - SQLMatters[^]

You can try this

DECLARE @txnid int
DECLARE @table table (Txnid INT,Items VARCHAR(15))

DECLARE cur CURSOR FOR
SELECT DISTINCT trans.Txnid FROM trans 

OPEN cur
FETCH NEXT FROM cur INTO @txnid

WHILE @@FETCH_STATUS =0
BEGIN 

	  INSERT INTO @table 
	  SELECT @txnid Txnid,STUFF((SELECT ','+items FROM Items WHERE itemid in(SELECT itemid FROM trans WHERE txnid=@txnid) FOR XML PATH('')),1,1,'')Items 
	  FETCH NEXT FROM cur INTO @txnid   
	 
END
SELECT * FROM @table
CLOSE cur
DEALLOCATE cur