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
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
) a
The 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]
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.
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
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)?