Post reply

Convert Horizontal Table to Vertical Table?

  • May 7, 2017 at 8:59 pm

    #323404

    Hi all, I have a question that, I'm query for a table:
    select (select count(*) as Clockin from A where r_Use='Clocking') as ClockIn,
    (select count(*) as ClockOut from A where r_Use=Clocking' and EndTime is NOT NULL) as ClockOut;
    so the table I have:
    ClockIn | ClockOut
    ===============
    1234      | 2345
    now I want to change my result to
    Status     | Value
    ===========
    ClockIn   | 1234
    ClockOut | 2345

    Pls help me,
    Thanks

  • May 7, 2017 at 9:50 pm

    #1941497

    SELECT id, ClockIn AS EventTime, 'IN' AS Event
    FROM
    (SELECT 1 AS id, 1234 AS ClockIn, 2345 AS Clockout) a
    UNION ALL
    SELECT id, ClockOut AS EventTime, 'OUT' AS Event
    FROM
    (SELECT 1 AS id, 1234 AS ClockIn, 2345 AS Clockout) a;

  • May 8, 2017 at 2:37 am

    #1941528

    SELECT x.[Status], [Value] = COUNT(*)

    FROM A

    CROSS APPLY (

    SELECT [Status] = CASE WHEN EndTime is NOT NULL THEN 'ClockOut' ELSE 'ClockIn' END

    ) x

    WHERE r_Use = 'Clocking'

    GROUP BY x.[Status]

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • May 8, 2017 at 6:04 am

    #1941566

    This should be efficient as it reads the table only once.

    WITH ClockCounts AS(
      SELECT COUNT(*)   AS ClockIn ,
        COUNT(EndTime) AS ClockOut
      FROM A
      WHERE r_Use='Clocking'
    )
    SELECT [Status], Value
    FROM ClockCounts
    CROSS APPLY( VALUES('ClockIn', ClockIn),
           ('ClockOut', ClockOut))up([Status], Value);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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