How do I display data using 3 tables but only use a distinct name.

Question

I have three tables, the set up is like this

Computer table-----

[key]  
(int) ComputerID   
(String) ComputerName  
(String) Department

Ticket table----

[key]  
(int) TicketID
[foreign key]  
(int) ComputerID  
(datetime) DownTimeStamp  
(int) runningStatus

Update ticket table---

[key]  
(int) UpdateID  
[foreign key]  
(int) TicketID  
(datetime) updateTimeStamp

The User creates a ticket and a new ticket is inserted into the table with the date time it was created. A user can come in and update the ticket, indicating whether someone is there to fix the computer, waiting for parts or that the computer is fixed. Each update to the ticket is recorded and a new row is inserted with the time of the edit, into the update ticket table. Depending on what the user is updating, I change the running status of the initial ticket.

The query I have right now is returning a row for each update for example:
Computer 1 updateTimeStamp
Computer 1 updateTimeStamp
Computer 2 updateTimeStamp
Computer 2 updateTimeStamp

How do I return a query that will return just one row with all of the updates? for example:
Computer 1 updateTimeStamp updateTimeStamp
Computer 2 upDateTimeStamp updateTimeStamp

In MySQL it would be easy with `GROUP_CONCAT()`, but SQLServer doesn't support that. Theres a workaround though, check [How to make a query with group_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — Havenard, Jul 06 '15 at 15:43
do you have a preference for how many updateTimeStamps you want returned? 5 most recent.. 10 most recent.. — JamieD77, Jul 06 '15 at 15:44
do you want the results grouped by `Computer, TicketID` or just `ComputerID` — JamieD77, Jul 06 '15 at 15:48
You would need to PIVOT your query results. There are plenty of examples on StackOverflow... — TT., Jul 06 '15 at 15:53
probably by TicketID, a computer can have many tickets and many updates. I just want a single computer, its ticket and all the updates to that ticket. I am trying to create a report with how long it took from the initial ticket creation to when it was fixed, from creation of ticket to a person is onsite fixing it and other details — user1314413, Jul 06 '15 at 16:02

score 0 · Answer 1 · answered Jul 06 '15 at 15:55

This will give you one column with all of the timestamps comma seperated

SELECT
    c.ComputerName,
    STUFF((SELECT
            ', ' + CONVERT(VARCHAR(20), ut.updateTimeStamp, 120)
           FROM
            Ticket t
            JOIN UpdateTicket ut ON t.TicketId = ut.TicketId         
           WHERE
            c.ComputerID = t.ComputerID
           ORDER BY
            ut.updateTimeStamp         
          FOR
           XML PATH('')
          ),1,2,'') AS UpdateTimeStamps
FROM
    Computer c

this will give you the result grouped by ComputerID, TicketID

SELECT
    c.ComputerName,
    t.TicketID,
    STUFF((SELECT
            ', ' + CONVERT(VARCHAR(20), ut.updateTimeStamp, 120)
           FROM
            UpdateTicket ut
           WHERE
            t.TicketID = ut.TicketID
           ORDER BY
            ut.updateTimeStamp         
          FOR
           XML PATH('')
          ),1,2,'') AS UpdateTimeStamps
FROM
    Computer c
    JOIN Ticket t ON c.ComputerID = t.ComputerID

Does that make the editTimeStamps into strings or are they still datetimes separated by a ,? — user1314413, Jul 06 '15 at 16:41
since it's in one column they get converted to strings 'yyyy-mm-dd hh:mm:ss' — JamieD77, Jul 06 '15 at 16:43

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

This can/will become one very big resultset since for each millisecond difference in ticket time you will have another column.
Are you sure that you want to do it like that?
Imagine 100 computers with 1 ticket each and 3 ticket updates (created, progress, closed), that alone gives you 300+ columns.
SQLServer has a limit of 4096 columns for a select statement (source)

If that is the way you want to do it, please take a look at Pivot
Examples about the usage of Pivot can be found here and here and here

(Posted as an answer since it became too big for a comment)

edited May 23 '17 at 10:26

Community

1
1

answered Jul 06 '15 at 15:56

Bernd Linde

2,098
2
16
22

in this case you would probably want to use row_number to order the dates then pivot on the row_number itself instead of the actual datetime. – JamieD77 Jul 06 '15 at 15:59

How do I display data using 3 tables but only use a distinct name.

2 Answers2