Post reply

Split row on two

  • March 20, 2015 at 9:51 am

    #398217

    Good morning,

    I’m trying to write query that will generate result set similar to #outcomeTbl from dataset that is in #Test1 table. My approach would be to try to pivot #test1 data to generate desired outcome but I’m not sure if this is the best way because #test1 might have thousands and thousands records. Any suggestions?

    Thank you

    IF (SELECT OBJECT_ID('tempdb..#Test1'))is not null

    DROP TABLE #Test1

    SELECT RecID, MyDate, ActualHours, BudgetHours, ActualOTHours, BudgetOTHours

    INTO #Test1

    FROM (

    SELECT 1, '2014-12-03 00:00:00.000', 40, 40, 10, 3 UNION ALL

    SELECT 2, '2014-12-03 00:00:00.000', 35, 40, 0, 0 UNION ALL

    SELECT 3, '2014-12-03 00:00:00.000', 40, 40,0, 5

    ) d (RecID, MyDate, ActualHours, BudgetHours, ActualOTHours, BudgetOTHours);

    IF (SELECT OBJECT_ID('tempdb..#OutcomeTbl'))is not null

    DROP TABLE #OutcomeTbl

    SELECT RecType, RecID, MyDate, ActualHours, ActualOTHours

    INTO #OutcomeTbl

    FROM (

    SELECT 'Actual', 1, '2014-12-03 00:00:00.000', 40, 10 UNION ALL

    SELECT 'Budget', 1, '2014-12-03 00:00:00.000', 40, 3 UNION ALL

    SELECT 'Actual', 2, '2014-12-03 00:00:00.000', 36, 0 UNION ALL

    SELECT 'Budget', 2, '2014-12-03 00:00:00.000', 0, 0 UNION ALL

    SELECT 'Actual', 3, '2014-12-03 00:00:00.000', 40, 0 UNION ALL

    SELECT 'Budget', 3, '2014-12-03 00:00:00.000', 40, 5

    ) W (RecType, RecID, MyDate, ActualHours, ActualOTHours);

    SELECT * FROM #Test1

    SELECT * FROM #OutcomeTbl

  • March 20, 2015 at 10:26 am

    #1785413

    I think this should do the trick:

    WITH t1 AS

    (

    SELECT * FROM #Test1

    CROSS APPLY (VALUES ('Actual'),('Budget')) ab(RecType)

    )

    SELECT

    RecType,

    recID, MyDate,

    CASE RecType WHEN 'Actual' THEN ActualHours WHEN 'Budget' THEN BudgetHours END AS ActualHours,

    CASE RecType WHEN 'Actual' THEN ActualOTHours WHEN 'Budget' THEN BudgetOTHours END AS BudgetHours

    FROM t1;

    Edit: code formatting

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • March 20, 2015 at 12:30 pm

    #1785451

    Thank you, exactly what I need

Viewing 3 posts - 1 through 2 (of 2 total)

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