Convert Horizontal Table to Vertical Table?

• hoangnguyennhh206

  SSC Enthusiast

  Points: 119

  More actions

  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

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  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;

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  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