0

I have a table called as tab where we store all the process running on machine in below format

proc_name     start_date     Duration
proc1         24-Jul-18      0:0
proc2         24-Jul-18      0:0
proc3         24-Jul-18      0:0
proc4         24-Jul-18      0:0
proc1         25-Jul-18      0:01
proc2         25-Jul-18      0:02
proc3         25-Jul-18      0:04
proc4         25-Jul-18      0:03
proc1         26-Jul-18      01:0
proc2         26-Jul-18      02:0
proc3         26-Jul-18      02:0
proc4         26-Jul-18      04:0

I want to see output as per below format:

Proc_name   Date1  Duration(of date1) Date2 Duration(of date2) Date3 Duration(of date3) 
proc1       24_jul-18  0:0            25-Jul-18 0:01           26-Jul-18 01:0
proc2       24_jul-18  0:0            25-Jul-18 0:02           26-Jul-18 02:0
proc3       24_jul-18  0:0            25-Jul-18 0:04           26-Jul-18 02:0
proc4       24_jul-18  0:0            25-Jul-18 0:03           26-Jul-18 04:0
4
  • Is your duration limited to max. 24 hours by any chance? This would allow a simpler solution. Please always provide your version of Postgres and a table definition showing actual data types and constraints (CREATE TABLE statement). Aug 2, 2018 at 1:53
  • Oh, I was assuming PostgreSQL, but you didn't actually say so. What is your RDBMS? Aug 2, 2018 at 15:40
  • My Bad...yes this is RDBMS...not a postgres...
    – anurag
    Aug 3, 2018 at 8:51
  • RDBMS is the general term: "Relational DataBase Management System". The question is, which RDBMS are you using? Aug 8, 2018 at 0:49

1 Answer 1

0

Details will depend on the database engine, but I think Pivot is the key search term you want. An approach that can probably be adapted for use with many database engines is described on SO here: https://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when. SQL Server has had a PIVOT operator since version 2008: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot.

Martin

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.