SQL database from row data to column

Question

I have data in my database(see below)

ID  STATUS  date    time
------------------------
223 IN  20180219    658
223 OUT 20180219    1822
223 IN  20180220    711
223 OUT 20180220    1806
223 IN  20180221    710
223 OUT 20180221    2007
223 IN  20180222    709
223 OUT 20180222    1805
223 IN  20180223    711
223 OUT 20180223    1703

I already used group by but I encountered an error. What method or query can I use to get this:

ID  date        IN  OUT
--- --------   ---- ----
223 20180219    658 1822
223 20180220    711 1806
223 20180221    710 2007
223 20180222    709 1805
223 20180223    711 1703

Are you using MySQL or SQL Server? Also, can you also share with us the query that you tried? — Giorgos Betsos
– Giorgos Betsos, Commented Apr 3, 2018 at 5:49
@dinesh your codes work very well.. can further explain on me what MAX use? — mr.jm
– mr.jm, Commented Apr 3, 2018 at 5:57
@JohnMarkArguilles, We have to group the values by Id and date. Using aggregate function only we can GROUP the values. So only I'm using MAX. — DineshDB
– DineshDB, Commented Apr 3, 2018 at 6:01

DineshDB · Accepted Answer · 2018-04-03 05:49:26Z

1

Try this:

SELECT ID,date
    ,MAX(CASE WHEN Status = 'IN' THEN time END) IN
    ,MAX(CASE WHEN Status = 'OUT' THEN time END) OUT
GROUP BY ID,date

answered Apr 3, 2018 at 5:49

DineshDB

6,2238 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Udai Krishnan · Accepted Answer · 2018-04-03 05:50:50Z

1

Please use PIVOT operation. Details link provided below: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

answered Apr 3, 2018 at 5:50

Udai Krishnan

765 bronze badges

Comments

Nishant Gupta · Accepted Answer · 2018-04-03 05:53:43Z

1

The solution for your problem Using Dynamic SQL Query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Status) 
            FROM Table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, date, ' + @cols + ' from 
            (
                select id
                    , Status
                    , date
                    , time
                from Table1
           ) x
            pivot 
            (
                 max(time)
                for Status in (' + @cols + ')
            ) p '

 execute(@query)

OUTPUT:

id  date      IN    OUT
223 20180219  658   1822
223 20180220  711   1806
223 20180221  710   2007
223 20180222  709   1805
223 20180223  711   1703

Follow the link to the demo:

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=edbafbfa0d6b0d210c81fd745d815224

answered Apr 3, 2018 at 5:53

Nishant Gupta

3,6761 gold badge13 silver badges20 bronze badges

1 Comment

Nishant Gupta Over a year ago

@mr.jm Try my solution since it is for any no. of statuses. If you add another status you don't have to change the query.

Sreenu131 · Accepted Answer · 2018-04-03 09:25:08Z

By Using Pivot

;With cte(ID,[STATUS],[date],[time])
AS
(
SELECT 223,'IN' , 20180219, 658  UNION ALL
SELECT 223,'OUT', 20180219, 1822 UNION ALL
SELECT 223,'IN' , 20180220, 711  UNION ALL
SELECT 223,'OUT', 20180220, 1806 UNION ALL
SELECT 223,'IN' , 20180221, 710  UNION ALL
SELECT 223,'OUT', 20180221, 2007 UNION ALL
SELECT 223,'IN' , 20180222, 709  UNION ALL
SELECT 223,'OUT', 20180222, 1805 UNION ALL
SELECT 223,'IN' , 20180223, 711  UNION ALL
SELECT 223,'OUT', 20180223, 1703
)
SELECT ID,[date],[IN],[OUT]
FROM
(
    SELECT * FROM cte
)AS Src
PIVOT
(MAX([time]) FOR [STATUS] IN([IN],[OUT])
)AS pvt

Result

ID  date        IN  OUT
------------------------
223 20180219    658 1822
223 20180220    711 1806
223 20180221    710 2007
223 20180222    709 1805
223 20180223    711 1703

Collectives™ on Stack Overflow

SQL database from row data to column

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related