November 5, 2012 at 5:09 am
Hi,
I have one Table
Userid Monthly Quaterly HalfYearly Yearly
----------------------------------------------------
1 30.00 0.00 0.00 0.00
2 0.00 300.00 0.00 0.00
I want display the data in gridview based on above table.
User PaymentTerm Amount
--------------------------------
Raja Monthly 30.00
Ram Quaterly 300.00
what is the query for above
November 5, 2012 at 5:14 am
What about
SELECT USERID, 'Monthly' as [PERIOD], SUM([MONTHLY] As [PERIOD_TOTAL]
UNION
SELECT USERID, 'Quaterly' as [PERIOD], SUM([Quaterly] As [PERIOD_TOTAL]
UNION
SELECT USERID, 'HealfYearly' as [PERIOD], SUM([HalfYearly] As [PERIOD_TOTAL]
UNION
SELECT USERID, 'Yearly' as [PERIOD], SUM([Yearly] As [PERIOD_TOTAL]
HTH,
B
November 5, 2012 at 7:05 am
Try this, unpivot logic:
CREATE TABLE #Amt(Id INT, Monthly Float,Quaterly Float,HealfYearly Float,Yearly Float)
INSERT INTO #Amt (Id ,Monthly ,Quaterly ,HealfYearly ,Yearly )
VALUES (1,100,200,400,800),(2,200,400,600,900)
--SELECT * FROM #Amt
SELECT ID, PaymentTerm, Amount
FROM
(
SELECT Id ,Monthly ,Quaterly ,HealfYearly ,Yearly FROM #Amt
) p
UNPIVOT
(Amount FOR PaymentTerm IN
(
Monthly,
Quaterly,
HealfYearly,
Yearly
)
)AS unpvt
DROP TABLE #Amt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply