Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

rohansinghrawat's avatar

Aggregate output from table with single column output

I have one table with this data

Id  UserId       walletId    open  close 
1	US000001	    1	            10.00	11.00
2	US000001	    2	            22.00	25.00
3	US000002	1	            10.00	11.00

I want output like this

Id UserId        wallet_1_open wallet_1_close wallet_2_open wallet_2_close
1   US000001   10.00              11.00                  22.00                  25.00
2  US000002  10.00              11.00                  0                          0

I have tried this query but failed

SELECT DISTINCT(user_id),
(
	CASE 
		 WHEN wallet_id= '1' THEN open 
	else 0
	 END
) as wallet_1_open,
(
	CASE WHEN wallet_id= '1' THEN close ELSE 0 END
) as wallet_2_close,
(
	CASE WHEN wallet_id= '2' THEN open ELSE 0 END
) as wallet_2_open ,
(
	CASE WHEN wallet_id= '2' THEN close ELSE 0 END
) as wallet_2_close from tbl;
0 likes
3 replies
Tray2's avatar

Can a user have the maximum of two wallets, or can a user have an more or less infinite number of wallets?

If it is a set number than it's doable with a single sql query, but if it is a dynamic number of wallets, it's not doable.

SELECT t1.user_id, 
      (SELECT t2.open FROM table t2 WHERE t2.user_id = t1.user_id ) walet_1_open,
      (SELECT t2.close FROM table t2 WHERE t2.user_id = t1.user_id ) walet_1_close,
      (SELECT t3.open FROM table t3 WHERE t3.user_id = t1.user_id ) walet_2_open,
      (SELECT t3.close FROM table t3 WHERE t3.user_id = t1.user_id ) walet_2_close,
FROM table t1;

To make this dynamic, you need to build the SQL first depending on your data, and then execute it. It is possible to do, but it's not a good solution.

rohansinghrawat's avatar

@Tray2 the number of wallet is same but is there any better approach instead of using subqueries because I was doing the same but i thought there must be some better query

Please or to participate in this conversation.