SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get dynamic column and give count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/08/2013 :  08:45:37  Show Profile  Reply with Quote
hello all,

i have query in which i will get data from different tables here i need to show like in example

For one profile ID

			ProfileID MembershipID AmtPaid  Status
			 11011     1234        1000   1
			 11011     1233        2000   0
			 11011     1232        1000   0 


here ProfileID ,MembershipID ,Paid i got data from different tables now the only thing i need to show dynamic column Status and
for Paid Column first row i need to give status As '1' and for the rest of rows need to be status zero '0' and same like another profile ID first Amountpaid row is 1 and rest belong to that profile Id is Zero
suggest me

P.V.P.MOhan

Edited by - mohan123 on 04/08/2013 08:46:37

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/08/2013 :  09:00:36  Show Profile  Reply with Quote
Means Do you want to latest MembershipID's status as 1?
DECLARE @tab TABLE(ProfileID INT, MembershipID INT, AmtPaid INt)
INSERT INTO @tab
SELECT 11011, 1234, 1000 union all
SELECT 11011, 1233, 2000union all
SELECT 11011, 1232, 1000 union all
SELECT 11012, 1256, 1000 union all
SELECT 11012, 1226, 2000

SELECT ProfileID, MembershipID, AmtPaid, CASE WHEN RN= 1 THEN 1 ELSE 0 END Status
FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY ProfileId ORDER BY MembershipID DESC) RN
		FROM @tab
	) t
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  00:57:10  Show Profile  Reply with Quote
Hi chandu it worked charm....thanks

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/09/2013 :  01:00:50  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

Hi chandu it worked charm....thanks

P.V.P.MOhan


Welcome

--
Chandu
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 04/09/2013 :  05:44:58  Show Profile  Reply with Quote
for fun
select *, case when MembershipID = max(MembershipID) over (partition by ProfileID) then 1 else 0 end from @tab
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000