How to Select Row to Column

Question

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

vinu512 SSCoach Points: 15729 More actions · Answer 1

I could have been more precise if you had given me some sample data to work with.

Anyways, you can accomplish what you are trying to do by using UNPIVOT. Here is how you can do it:

;With CTE

As

(SELECT

A1.TotalAmount * 20 / 100 AS TutionFees

,A1.TotalAmount * 20 / 100 AS CorpusFund

,A1.TotalAmount * 20 / 100 AS BuildingFund

,A1.TotalAmount * 15 / 100 AS PlantCapital

,A1.TotalAmount * 15 / 100 AS DevelopmentFund

,A1.TotalAmount * 10 / 100 AS CognipiveFund

,TotalAmount

FROM(SELECT

SUM(A.Amount) AS TotalAmount

FROM (SELECT

CASE WHEN CR.BillNo = B.BillNo THEN 0 ELSE BD.Amount END AS Amount

FROM SStudentBilling B

INNER JOIN SStudentBillingDetail BD ON B.BillNo = BD.BillNoAND ParticularID = 7

LEFT OUTER JOIN StudentCancelRefund CR ON B.BillNo = CR.BillNo

) AS A

) AS A1)

SELECT Description, Amount

FROM

(SELECT TutionFees, CorpusFund, BuildingFund, PlantCapital

FROM CTE) p

UNPIVOT

(Amount FOR Description IN

(TutionFees, CorpusFund, BuildingFund, PlantCapital)

)AS unpvt

Hope this helps.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉