Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieve Data with condition Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 5:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 5, 2012 5:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 165, Visits: 678
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
Posted Monday, November 5, 2012 7:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:47 AM
Points: 219, Visits: 336
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 »

Add to briefcase

Permissions Expand / Collapse