How to get dynamic column and give count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get dynamic column and give count

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-08 : 08:45:37

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
[CODE]
ProfileID MembershipID AmtPaid Status
11011 1234 1000 1
11011 1233 2000 0
11011 1232 1000 0
[/CODE]

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-08 : 09:00:36

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