Converting rows into column based on different person

Transact-SQL
1 
Create table #commission
(
    TransactionID int,
    Product char(10),
    Quantity int,
    UnitPrice money,
    TotalPrice money,
    Casher char(10),
    [Commission_%] int,
    commission_amt money
)

insert into #commission 
values (10011, 'Apple', 2, 75, 150, 'JOHN', 10, 15),
       (10012, 'Ball', 3, 15, 45, 'JOHN', 10, 4.5),
       (10011, 'Apple', 2, 75, 150, 'LUCY', 5, 7.5),
       (10012, 'Ball', 3, 15, 45, 'LUCY', 5, 2.25)

select * 
from #commission
![image|690x103](upload://pKfHdnnXoHEf7cmJJwP6yfzkIo9.png) 
Original data:							
TransactionID	Product	Quantity	UnitPrice($)	TotalPrice	Cashier	COMMISSION_%	COMMISSION AMT($)
10011	Apple         	2	75	150	JOHN   	10%	15
10012	Ball           	3	15	45	JOHN      	10%	4.5
10011	Apple           	2	75	150	LUCY        	5%	7.5
10012	Ball           	3	15	45	LUCY         	5%	2.25
									
![image|690x50](upload://2W3YVpvhiI7Xb7q4AbMBWcQBjam.png) 
Expected Result data:										
TransactionID	Product	Quantity	UnitPrice($)	TotalPrice	Cashier	Commission_%	Commission AMT($)	Cashier	Commission_%	Commission AMT($)
10011	Apple         	2	75	150	JOHN   	10%	15	LUCY        	5%	7.5
10012	Ball           	3	15	45	JOHN      	10%	4.5	LUCY         	5%	2.25

I tried Dynamic Pivot as well. but not able to get result like shown in the picture. May be I was not able to use it properly. 
when using Dynamic Pivot, I got below result as shown below:
got it using Dynamic Pivot:					
TranID	qty	UnitPrice($)	TotPrice	JOHN	LUCY
10011	2	    75	          150	15	          7.5
10012	3	    15	            45	4.5	         2.25


it would be greatly appreciated if someone help me on it.
2

The data should really be normalized into transactions and commisions.
Start with something like:

WITH Transactions
AS
(
	SELECT DISTINCT C.TransactionID, C.Product, C.Quantity, C.UnitPrice, C.TotalPrice
	FROM #commission C
)
,Commissions
AS
(
	SELECT C.TransactionID, C.Casher, C.[Commission_%], C.commission_amt
		,ROW_NUMBER() OVER (PARTITION BY C.TransactionID ORDER BY C.commission_amt DESC) AS rn
	FROM #commission C
)
,CommisionPivot
AS
(
	SELECT C.TransactionID
		,MAX(CASE WHEN C.rn = 1 THEN C.Casher END) AS Casher1
		,MAX(CASE WHEN C.rn = 1 THEN C.[Commission_%] END) AS Percent1
		,MAX(CASE WHEN C.rn = 1 THEN C.commission_amt END) AS Amt1
		,MAX(CASE WHEN C.rn = 2 THEN C.Casher END) AS Casher2
		,MAX(CASE WHEN C.rn = 2 THEN C.[Commission_%] END) AS Percent2
		,MAX(CASE WHEN C.rn = 2 THEN C.commission_amt END) AS Amt2
		,MAX(CASE WHEN C.rn = 3 THEN C.Casher END) AS Casher3
		,MAX(CASE WHEN C.rn = 3 THEN C.[Commission_%] END) AS Percent3
		,MAX(CASE WHEN C.rn = 3 THEN C.commission_amt END) AS Amt3
		--etc
	FROM Commissions C
	GROUP BY C.TransactionID
)
SELECT T.TransactionID, T.Product, T.Quantity, T.UnitPrice, T.TotalPrice
	,P.Casher1, P.Percent1, P.Amt1
	,P.Casher2, P.Percent2, P.Amt2
	,P.Casher3, P.Percent3, P.Amt3
	--etc
FROM Transactions T
	JOIN CommisionPivot P
		ON T.TransactionID = P.TransactionID;
3

Hi Ifor,

Thanks a lot. It helped a lot. One more thing, can we make it dynamic based on cashier. eg if 1 cashier then show one cashier info only and if 2 cashier then show info of two cashier and so on. is it possible?

4
  1. All rows in a table have to have the same number of columns.
  2. If possible, dynamic SQL is best avoided.

I would be inclined to use static SQL with the maximum number of likely cashers and sort the display out in the front end.