none
How to display data on separate rows in one RRS feed

  • Question

  • I have a table with the following setup

    ID            InOut_Status     InOut_Datetime

    1              IN                      9/12/2017 8:00

    2              IN                      9/12/2017 10:00

    1              OUT                   9/12/2017 1:00

    2              OUT                   9/12/2017 3:00

    I want to be able to see both status and date on the same row vs separate rows for example

    ID            In_Status             In_Datetime                      Out_Status         Out_Datetime

    1              IN                       9/12/2017 8:00                 OUT                   9/12/2017 1:00

    2              IN                       9/12/2017 10:00               OUT                   9/12/2017 3:00

    I already added the In_Status, In_Datetime, Out_Status, Out_Datetime to the table.  Is this possible?  If so, what’s the best way to get this to work?

    Any assistance would be greatly appreciated.

    Tuesday, September 12, 2017 11:27 AM

All replies

  • Hello,

    A simple self-join should give you the data;

    SELECT I.*, O.*
    FROM YourTable AS I
         INNER JOIN
         YourTable AS O
             ON I.ID = O.ID
    WHERE I.InOut_Status = 'IN'
          AND O.InOut_Status = 'OUT'
    




    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Tuesday, September 12, 2017 11:33 AM
  • CREATE TABLE #t (id INT, InOut_Status VARCHAR(3),InOut_Datetime DATETIME)

    INSERT INTO #t VALUES (1,'IN','20170912 08:00')
    INSERT INTO #t VALUES (2,'IN','20170912 10:00')
    INSERT INTO #t VALUES (1,'OUT','20170912 01:00')
    INSERT INTO #t VALUES (2,'OUT','20170912 03:00')

    SELECT * FROM #t T1 JOIN #t T2 ON T1.id=T2.id
    WHERE T1.InOut_Status='IN'
    AND T2.InOut_Status ='OUT'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, September 12, 2017 11:40 AM
    Answerer
  • Thanks for your response.  The data is displaying the way I want it to display.  But is there a way to only show the most current InOut_Datetime for each ID as well?  For example, I would like to show only the most recent IN and OUT time and if not checked out yet, the InOut_Status and InOut_Datetime for Out will be blank.  Is this possible? Do I have to submit another question for this?
    Tuesday, September 12, 2017 12:35 PM
  • No, please post sample data along with desired result

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, September 12, 2017 12:45 PM
    Answerer
  • Thanks for your response.  The data is displaying the way I want it to display.  But is there a way to only show the most current InOut_Datetime for each ID as well?  For example, I would like to show only the most recent IN and OUT time and if not checked out yet, the InOut_Status and InOut_Datetime forOut will be blank.  Is this possible? Do I have to submit another question for this?
    Please post the sample output for this .

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Tuesday, September 12, 2017 1:14 PM
  • Hope this would help;

    CREATE TABLE #t (id INT, InOut_Status VARCHAR(3),InOut_Datetime DATETIME)
    
    INSERT INTO #t VALUES (1,'IN','20170912 08:00')
    INSERT INTO #t VALUES (2,'IN','20170912 10:00')
    --INSERT INTO #t VALUES (1,'OUT','20170912 01:00')
    INSERT INTO #t VALUES (2,'OUT','20170912 03:00')
    
    
    
    ;with wcte as 
    (
    	Select *, Row_Number() Over (Partition By ID Order by ID,InOut_Datetime DESC) as RNO 
    	from #t a
    	where a.InOut_Status = 'IN'
    ) Select a1.id,a1.InOut_Status,a1.InOut_Datetime , 'OUT' as InOut_Status2,(Select Top(1) t1.InOut_Datetime from #t t1 where t1.id = a1.id and t1.InOut_Status='OUT' Order by t1.InOut_Datetime Desc) as InOut_Datetime2 
    from wcte a1
    Where a1.RNO = 1
    
    
    Drop table #t
    


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    Tuesday, September 12, 2017 1:19 PM
  • Hi,

    You can also do like below way.

    This is not give same result as you want but it is give work around

    CREATE TABLE #t (id INT, InOut_Status VARCHAR(3),InOut_Datetime DATETIME)

    INSERT INTO #t VALUES (1,'IN','20170912 08:00')
    INSERT INTO #t VALUES (2,'IN','20170912 10:00')
    INSERT INTO #t VALUES (1,'OUT','20170912 01:00')
    INSERT INTO #t VALUES (2,'OUT','20170912 03:00')


    select *
    from
    (
    select id,InOUt_Status,InOut_Datetime
    from #t
    )src
    pivot
    (
    min(InOut_Datetime)
    for InOut_Status in ([IN],[OUT])
    )
    piv;

    • Proposed as answer by Bhadresh Vasani Tuesday, September 12, 2017 1:33 PM
    • Unproposed as answer by Jingyang LiModerator Tuesday, September 12, 2017 3:10 PM
    • Proposed as answer by User TBS Thursday, September 14, 2017 5:18 AM
    Tuesday, September 12, 2017 1:32 PM
  • Bhadresh,

    This is exactly what I need.  However, I need to select all of the columns instead of just ID, InOut_Status, and InOut_Datetime.  There are actually more columns, I just included a few for example. How can I do this using the pivot and return the same results or is it possible?

    Tuesday, September 12, 2017 3:13 PM
  • Hi,

    Can you give any example or sample data for more columns?

    so i can help you.

    Thanks,

    Bhadresh

    Wednesday, September 13, 2017 7:26 AM
  • Thank you for the answer. 
    Monday, October 21, 2019 7:26 AM