Retrieve Data with condition

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Retrieve Data with condition

Retrieve Data with condition

Rate Topic

Display Mode

Topic Options

Author

Message

sidharthgs6

Posted Monday, November 05, 2012 5:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:22 PM
Points: 6, Visits: 14

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

Post #1381003

bleroy

Posted Monday, November 05, 2012 5:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:46 AM
Points: 138, Visits: 549

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

Post #1381006

deepkt

Posted Monday, November 05, 2012 7:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:42 AM
Points: 202, Visits: 286

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

Post #1381066

« Prev Topic | Next Topic »

Permissions