SQLTeam.com | Weblogs | Forums

How to loop on the number of selected codes

sql2008

#1

How to loop on the number of selected codes and assign the code description to a declare variables

So based on the data below. if I only have 2 languages flagged for display only 2 will be assigned not 3.

The language description table looks like this:
8332 05 US LARGE GREEN CHAIR 1 Y
8332 05 MX SILLA VERDE GRANDE 2 Y
8332 05 CN 大綠色的椅子 3 N

DECLARE
@counter INT = 1,
@max INT = 0,
@language_desc_1 nvarchar(100),
@language_desc_2 nvarchar(100),
@language_desc_3 nvarchar(100),
@language_desc_4 nvarchar(100),
@language_desc_5 nvarchar(100),
@language_desc_6 nvarchar(100),
@language_desc_7 nvarchar(100),
@language_desc_8 nvarchar(100),
@language_desc_9 nvarchar(100),
@language_desc_10 nvarchar(100),

SELECT @max = COUNT(Language_Code) FROM Products_Descriptions as pd where pd.Display_Flag = 'Y'

-- Loop
WHILE @counter <= @max
BEGIN
-- I need to assign the number of select languages to the declare variables (@language_desc_)
SELECT @language_desc_1
FROM Products_Descriptions

SET @counter = @counter + 1

END


#2

Q: How to Loop?
A: Don't!select @language_desc_1 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_2 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_3 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_4 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_5 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_6 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_7 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_8 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_9 = case when a.RowNum <= @max then a.Language_code else null end, @language_desc_10 = case when a.RowNum <= @max then a.Language_code else null end from ( select Language_code, ROW_NUMBER() over (order by Display_flag DESC) RowNum from Product_descriptions ) aThe subquery imposes an order on the data such that the 'Y' values are first. I suppose that you could put the COUNT() logic in the subquery as well. (Details, details)

[Soapbox]
"The language description table looks like this:"
No, the DATA looks like this. You never told us what the TABLE looks like.
[/Soapbox]


#3

Thanks BustazKool..

Sorry the language description table would be "Products_Descriptions".

Are you saying I do not have to do a loop?

Was not sure what you were saying about counter.


#4

Hi

I made this change but looks like the statement is only display the last Row Number.

BEGIN
select
@language_desc_1 = case when a.RowNum = 1 then a.Language_code else null end,
@language_desc_2 = case when a.RowNum = 2 then a.Language_code else null end,
@language_desc_3 = case when a.RowNum = 3 then a.Language_code else null end,
@language_desc_4 = case when a.RowNum = 4 then a.Language_code else null end,
@language_desc_5 = case when a.RowNum = 5 then a.Language_code else null end
from
(
select
Language_code, ROW_NUMBER() over (order by Sort_Code) RowNum
from
Products_Descriptions
where Display_On_Label_Flag = 'Y'
) a
END

select @language_desc_1
select @language_desc_2


#5

Well, it looks like I picked the wrong week to give up sniffing glue...
I have made a hash of it BUT hope springs anew. Can the fifth column be constrained to unique values 1 thru 10 at least within one phrase number (8332)?