Post reply

Looping through a returned SQL query and extracting data based on a condition on the fly

  • May 18, 2011 at 4:34 am

    #145453

    Hi all

    I have a SQL query that returns data such as below (but there is thousands of rows):

    BI N White ISP001 4.11 3.02 5.22

    BI J Smith ISP333 2.0 4.08 1.01

    JAVA J Smith ISP333 3.02 5.11 4.22

    DOT NET P Barn AA841 4.22 4.11 5.05

    BI P Barn AA841 6.22 5.33 6.22

    I need to somehow loop through this data and extract the data that belongs to either BI, JAVA or DOT NET. Now of course I would use a WHERE clause for this, but it is creating the loop part to loop through the BI, JAVA, DOT NET that I'm stuck on. I'd prefer not to create numerous queries or do it statically (i.e. WHERE firstColumn = 'BI' etc). Something on the fly would be better?

    Is there a BI task that will do this? Please help?

    Thanks in advance.

  • May 18, 2011 at 4:39 am

    #1326312

    Lookup "Conditional Split".

    http://msdn.microsoft.com/en-us/library/ms137886.aspx

    http://www.bimonkey.com/2009/06/the-conditional-split-transformation/

    http://sqlblog.com/blogs/andy_leonard/archive/2009/02/20/ssis-expression-language-and-the-conditional-split-transformation.aspx

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 18, 2011 at 4:40 am

    #1326315

    Hi skcadavre

    Thank you, I shall look these links up.

  • May 18, 2011 at 4:48 am

    #1326318

    Hi skcadavre

    I've just had a quick look. In the conditional split you are having to hardcode the condition, i.e. Dept = 'BI'. So I would be manually working out the depts before hand. I would like to use a method that detects how many different teams are present in the returned sql query data and then do the extracting of data on that. I am expecting to use some sort of loop to extract data that belongs to certain teams.

    Any suggestions???

  • May 18, 2011 at 5:24 am

    #1326339

    drew.. (5/18/2011)

    Hi skcadavre

    I've just had a quick look. In the conditional split you are having to hardcode the condition, i.e. Dept = 'BI'. So I would be manually working out the depts before hand. I would like to use a method that detects how many different teams are present in the returned sql query data and then do the extracting of data on that. I am expecting to use some sort of loop to extract data that belongs to certain teams.

    Any suggestions???

    The idea of a conditional split is that you can process your data in bulk, splitting off the different "teams" and processing them in whatever way is required. All this requires is that you know how you want to process each "team".

    If you loop over the data, you still need to know what to do with each "team". But now instead of just partitioning out the data and working on it in parallel, you are forced to re-examine each individual row of data until you have finished getting each "team"'s data processed.

    Guess which is faster? 😉

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply