none
Iterate table and switch depanding on every row RRS feed

  • Question

  • Hi everyone,

    I have table with one column where I have following records:

    U_165, G_155, C_25, U_856,...

    And I would like to iterate throw this table and depanding on first character look into different table and select something.

    Could you help me with the iteration and switch case statement? :(

    Thank you for your replies.

    Monday, March 26, 2012 5:21 PM

Answers

  • This is how you would do it. (Typed in notepad, check for syntax)

    SELECT
        CASE SUBSTRING(MyCol, 1, 1) WHEN 'U' THEN (Select T2.Col1 FROM MyTable2 T2 WHERE T2.Col2 = 'U')
    				WHEN 'G' THEN (Select T3.Col1 FROM MyTable3 T3 WHERE T3.Col2 = 'G')
    	END AS MyValue
    FROM Table1

    But Can you elaborate what you are trying to achieve.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Marked as answer by KJian_ Monday, April 2, 2012 2:36 AM
    Monday, March 26, 2012 5:51 PM

All replies

  • Would you please explain a little more detailed why you need to iterate through the table?

    Wouldn't it be better to use a WHERE clause and fillter all rows with a common first character?

    Monday, March 26, 2012 5:34 PM
  • You can join with all the related tables and select the correct columns based on the first character of the Transaction table as shown in the example:

    declare @U table (Code varchar(10), Descr varchar(50))
    declare @G table (Code varchar(10), Descr varchar(50))
    declare @C table (Code varchar(10), Descr varchar(50))
    
    insert @U select 'U_165', 'U_165 Description' union all
    select 'U_856', 'U_856 Description'
    
    insert @G select 'G_155', 'G_155 Description'
    
    insert @C select 'C_25', 'C_25 Description'
    
    declare @Trans table (Code varchar(10))
    
    insert @Trans
    select 'U_165' union all
    select 'U_856' union all
    select 'G_155' union all
    select 'C_25'
    
    
    select a.Code,
       case left(a.Code, 1) when 'U' then b.Descr
          when 'G' then c.Descr
          when 'C' then d.Descr
       end MappedDescr
    from @Trans a left join @U b on a.Code = b.Code
    left join @G c on a.Code = c.Code
    left join @C d on a.Code = d.Code


    The simpler the solution the stronger it is
    If this post answers you, please mark it as answer..
    If this post is useful, please vote it as useful..

    Monday, March 26, 2012 5:42 PM
  • In T-SQL i can rephrase it as

    Select t.[one column], ...

    from [table with one column] t

    join [other table] on left (t.[one column],1) = [other table].[other column]

    But sure we can be more helpful if you can provide more details.


    Serg

    Monday, March 26, 2012 5:50 PM
  • This is how you would do it. (Typed in notepad, check for syntax)

    SELECT
        CASE SUBSTRING(MyCol, 1, 1) WHEN 'U' THEN (Select T2.Col1 FROM MyTable2 T2 WHERE T2.Col2 = 'U')
    				WHEN 'G' THEN (Select T3.Col1 FROM MyTable3 T3 WHERE T3.Col2 = 'G')
    	END AS MyValue
    FROM Table1

    But Can you elaborate what you are trying to achieve.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Marked as answer by KJian_ Monday, April 2, 2012 2:36 AM
    Monday, March 26, 2012 5:51 PM
  • Any progress?

    Kalman Toth SQL SERVER & BI TRAINING

    Saturday, March 31, 2012 11:27 PM
    Moderator
  • Hi,

    Use substring() function and use Case for the iteration.....

    Sunday, April 1, 2012 8:23 AM