quote:

---

Originally posted by satheesh

Hi

I have policy and commission table. There are up to 3 levels of commission for each policy and i want to select all commission in a row by policyid

for eg :
select policy.policyid,commission.amount,commission.level
from policy
inner join commission
on commission.policyid = policy.policyid

Result

Policyid Commission level
50470 9.056839 2
50470 31.011067 3
50470 7.185093 0
23355 6.8347 0
23355 76.989 2

But i want to display like below

policyid level0 level2 level3
50470 7.185093 9.056839 31.011067
23355 6.8347 76.989

Any help will be highly appreciated.


Thanks

Regards,
SG

---

-- *** Test Data ***
CREATE TABLE #Commission
(
	Policyid int NOT NULL
	,Commission decimal(8,6) NOT NULL
	,[level] tinyint NOT NULL
);
INSERT INTO #Commission
SELECT 50470, 9.056839, 2
UNION ALL SELECT 50470, 31.011067, 3 
UNION ALL SELECT 50470, 7.185093, 0 
UNION ALL SELECT 23355, 6.8347, 0
UNION ALL SELECT 23355, 76.989, 2
-- *** End Test Data ***

SELECT Policyid
	,COALESCE([0], 0) AS Level0
	,COALESCE([2], 0) AS Level2
	,COALESCE([3], 0) AS Level3
FROM
(
	SELECT Policyid, Commission, [level]
	FROM #Commission
) S
PIVOT
(
	MIN(Commission)
	FOR [level] IN ([0], [2], [3])
) P