Home Forums SQL Server 2012 SQL 2012 - General Issue with query RE: Issue with query

  • February 22, 2017 at 5:29 pm

    #1929876

    mcfarlandparkway - Wednesday, February 22, 2017 5:19 PM

    I have a table where I am trying to write a query in SSIS package using execute sql task to get date ranges form this table

    col1     col2      
    Emp    2/1/2016
    Std      4/1/2016

    I am trying to retrive the same col2 twice using different conditions?

    select col2 form table where col1 = Emp
    union all
    select col2 form table where col1 = Std

    In this way I will get two rows, But in Execute sql ask Resultset single row will notwork as from query we are getting two rows..

    Is there a way to modify the query using subquery as to get single column with two dates?

    Your UNION query, as you can see if you run it, will give both values you want in a one-column output.

    A sneaky way to do it is

    SELECT MAX(CASE col1 WHEN 'Emp' THEN col2 ELSE NULL END) as EmpCol2,
           MAX(CASE col1 WHEN 'Std' THEN col2 ELSE NULL END) as StdCol2
      FROM table

    Note things can get wonky if you have more than one of either/both values in the table.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service