Stored Proc - Using while loop in select

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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc - Using while loop in select

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-11-15 : 10:26:19

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

52326 Posts

Posted - 2012-11-15 : 10:52:22

[code]
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
[/code]

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

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-11-15 : 11:36:54

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 23:48:49

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources