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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored Proc - Using while loop in select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 11/15/2012 :  10:26:19  Show Profile  Reply with Quote
I have up to 10 items that can be associated to one loan. There are multiple fields that can be in those 10 items. I want to loop through with a variable to write the fields once instead of 10x each. Here is what I have tried and failed miserably on so far:

DECLARE @x as int
SET @x = 1
select
R.MEM_NBR,
WHILE @x <= 10
BEGIN
max(case when R.rn = @x then R.ID end) as Collateral_ + CAST(@x as Varchar(1)),
max(case when R.rn = @x then R.AN end) as Agt_ + CAST(@x as Varchar(1)),
...
END
R.LN_NBR
from R

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/15/2012 :  10:52:22  Show Profile  Reply with Quote

SELECT m.*,Agt_1,Agt_2,...,Agt_10
FROM
(
SELECT MEM_NBR,LN_NBR,
[1] AS Collateral_1,
[2] AS Collateral_2,
[3] AS Collateral_3,
...
[10] AS Collateral_10
FROM (SELECT MEM_NBR,LN_NBR,rn,ID FROM R) r1
PIVOT (MAX(ID) FOR rn IN ([1],[2],[3],..,[10]))p
)m
INNER JOIN 
(
SELECT MEM_NBR,LN_NBR,
[1] AS Agt_1,
[2] AS Agt_2,
[3] AS Agt_3,
...
[10] AS Agt_10
FROM (SELECT MEM_NBR,LN_NBR,rn,AN FROM R) r2
PIVOT (MAX(AN) FOR rn IN ([1],[2],[3],..,[10]))q
)n
ON n.MEM_NBR = m.MEM_NBR
AND n.LN_NBR = m.LN_NBR


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 11/15/2012 :  11:36:54  Show Profile  Reply with Quote
For every item (Collateral_x and Agt_x) do I have to do a separate select?

If that is the case I can do it this way:

max(case when R.rn = 1 then R.COLLATERAL_ID end) as Collateral_1,
max(case when R.rn = 1 then R.AGT end) as Agt_1,
max(case when R.rn = 1 then R.NextItem end) as NextItem_1,
max(case when R.rn = 1 then R.NextItem end) as NextItem_1,
max(case when R.rn = 2 then R.COLLATERAL_ID end) as Collateral_2,
max(case when R.rn = 2 then R.AGT end) as AGt_2,
max(case when R.rn = 1 then R.NextItem end) as NextItem_2,
max(case when R.rn = 1 then R.NextItem end) as NextItem_2,

I was hoping to cut down the number of statments using variables in a sp, so is that not possible?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/15/2012 :  23:48:49  Show Profile  Reply with Quote
quote:
Originally posted by murrayb3024

For every item (Collateral_x and Agt_x) do I have to do a separate select?

If that is the case I can do it this way:

max(case when R.rn = 1 then R.COLLATERAL_ID end) as Collateral_1,
max(case when R.rn = 1 then R.AGT end) as Agt_1,
max(case when R.rn = 1 then R.NextItem end) as NextItem_1,
max(case when R.rn = 1 then R.NextItem end) as NextItem_1,
max(case when R.rn = 2 then R.COLLATERAL_ID end) as Collateral_2,
max(case when R.rn = 2 then R.AGT end) as AGt_2,
max(case when R.rn = 1 then R.NextItem end) as NextItem_2,
max(case when R.rn = 1 then R.NextItem end) as NextItem_2,

I was hoping to cut down the number of statments using variables in a sp, so is that not possible?


what do you mean by every item? unless you show how the data is in table we will not be able to understand how the data is

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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