How to return multiple rows in sql from same table

Pretty new here and could really use some help. I have a table with

TimeID(pk), DateEntry(date), EntryTiem(time), 
ProjID(int), ProjName(varchar), Phone(bit), 
Research(bit), Notes(varchar), ProjActive(bit), 
TimeDateStamp(date).

I’m trying to return multiple rows of data into each ProjName but am not having any luck.
Here is what I’ve come up with so far…

  • SSMS 2008 queries execution timeout does not allow changing anything
  • SQL Update Table By Joining Same Table
  • SQL Server Advance Forward X Rows With Loop Around
  • How to retrieve field names from temporary table (SQL Server 2008)
  • SQL using count, error “cannot convert * to int”
  • How to select max value from n number linked tuples
  • SELECT ProjName AS 'Project Name',  
        (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
         + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
         FROM WorkTime
         WHERE Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL
         ) AS 'Total Time No PMRE',     
        (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2) 
         + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
         FROM WorkTime
         WHERE Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL
         ) AS 'Total Time Phone',
         (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
          + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)  
          FROM WorkTime
          WHERE Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL
          ) AS 'Total Time Research',
          (SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
           + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
           FROM WorkTime
           WHERE EntryTime IS NOT NULL
           ) AS 'TotalTime'
    FROM WorkTime
    WHERE EntryTime IS NOT NULL
    GROUP BY ProjName   
    

    Which returns the following…

    Project Name   Total Time No PMRE   Total Time Phone   Total Time Research   Total Time
    AAAA           19:06:15             2:00:00            1:00:06               22:06:21
    BBBB           19:06:15             2:00:00        1:00:06               22:06:21
    CCCC           19:06:15             2:00:00        1:00:06               22:06:21
    DDDD           19:06:15             2:00:00        1:00:06               22:06:21
    

    Which is wrong. The amount in each column is the total for that condition with all of the ProjName added together instead of separated out individually.

    This is what the output should be…

    Project Name   Total Time No PMRE   Total Time Phone   Total Time Research   Total Time
    AAAA           00:00:19             0:00:00            0:00:00               00:00:19
    BBBB           00:00:04             0:00:00        0:00:00               00:00:04
    CCCC           03:00:00             2:00:00        1:00:06               06:06:06
    DDDD           16:05:52             0:00:00        0:00:00               16:05:52
    

    Does anyone have any suggestions or could help point me in the right direction? Thanks!

  • Trouble with a CASE statement in a sql server stored procedure
  • update datetime field by making values utc sql
  • Dynamic update for different colums using between two date like calendar date
  • How to move Azure SQL Database files to different location?
  • How to convert a byte into datetime in C#?
  • How would I design a data warehouse to store data in cubes with SSAS?
  • 2 Solutions collect form web for “How to return multiple rows in sql from same table”

    I think that what you are looking for is a conditional SUM:

    SELECT ProjName AS 'Project Name',  
        SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time No PMRE',
        SUM(CASE WHEN (Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Phone',
        SUM(CASE WHEN (Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Research',
        SUM(DATEDIFF(second, 0, EntryTime)) AS 'TotalTime'
    FROM WorkTime
    WHERE EntryTime IS NOT NULL
    GROUP BY ProjName
    

    Add your formatting code to the result of SUM and you will be fine

    Edit

    Answering your comment for the format, take your format “template”:

    CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
     + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
    

    and replace every occurence of SUM(DATEDIFF(second, 0, EntryTime) for SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END)

    Or, you can add the formats in a query that wraps the source query, like this:

    SELECT CAST([Total Time No PMRE] / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST([Total Time No PMRE] / 60 % 60 AS varchar(2)),2)
        + ':' + RIGHT('0' + CAST(([Total Time No PMRE] % 60 AS varchar(2)), 2)
    ... -- The other columns
    FROM (
        SELECT ProjName AS 'Project Name',
            SUM(CASE WHEN (Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time No PMRE',
            SUM(CASE WHEN (Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Phone',
            SUM(CASE WHEN (Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL) THEN DATEDIFF(second, 0, EntryTime) ELSE NULL END) AS 'Total Time Research',
            SUM(DATEDIFF(second, 0, EntryTime)) AS 'TotalTime'
        FROM WorkTime
        WHERE EntryTime IS NOT NULL
        GROUP BY ProjName
     ) AS ds
    

    Hope this helps you

    You need to correlate these subqueries to something in the outer query, probably the same thing you’re grouping on:

    SELECT ProjName AS 'Project Name',  
        (
            SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
            + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)
            FROM WorkTime
            WHERE Phone = 0 AND Research = 0 AND EntryTime IS NOT NULL
                AND ProjName = a.ProjName -- Correlate to outer query
        ) AS 'Total Time No PMRE',     
        (
            SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2) 
            + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
            FROM WorkTime
            WHERE Phone = 1 AND RESEARCH = 0 AND EntryTime IS NOT NULL
                AND ProjName = a.ProjName -- Correlate to outer query
        ) AS 'Total Time Phone',
        (
            SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
            + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2)  
            FROM WorkTime
            WHERE Phone = 0 AND RESEARCH = 1 AND EntryTime IS NOT NULL
                AND ProjName = a.ProjName -- Correlate to outer query
        ) AS 'Total Time Research',
        (
            SELECT CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 3600 AS varchar(12)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) / 60 % 60 AS varchar(2)),2)
            + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(second, 0, EntryTime)) % 60 AS varchar(2)), 2) 
            FROM WorkTime
            WHERE EntryTime IS NOT NULL
                AND ProjName = a.ProjName -- Correlate to outer query
        ) AS 'TotalTime'
    FROM WorkTime a -- Add alias
    WHERE EntryTime IS NOT NULL
    GROUP BY ProjName  
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.