Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
SQL
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
Posted
Updated 17-Oct-16 21:52pm
v2
Comments
Tomas Takac 16-Oct-16 15:53pm    
which database engine? which version? what error message?

You can try this

SQL
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

SQL

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900