Combine multiple rows to one row with multiple columns

Question

I have this query:

SELECT 
    bt AS id, 
    SUM(tempo) AS totalDowntime, 
    COUNT(manut.tempo) AS ocorrences, 
    MAX(data) AS date
FROM
    (SELECT 
         bt, DATEDIFF(SECOND, LAG(data, 1) OVER (ORDER BY data), data) AS tempo, 
         status, data
     FROM     
         [machining].[dbo].[manutencao]
     WHERE
         data > '2019-10-28' AND data <= '2019-11-05' 
         AND CONVERT((DATEPART(dw, data) + @@DATEFIRST) % 7) NOT IN (0, 1)
         AND bt IN (52)
     GROUP BY
         bt, data, status) manut
WHERE
    status = 1
GROUP BY
    bt, status, 
    DATEPART(yy, data), DATEPART(mm, data), DATEPART(dd, data) 
ORDER BY
    bt ASC

which returns something like this:

| id | totalDowntime | ocurrences | date                    |
+----+---------------+------------+-------------------------+
| 52 | 11909         | 19         | 2019-10-28 14:01:58.000 |
| 52 | 3980          | 12         | 2019-10-29 23:25:00.000 |
| 52 | 158           | 2          | 2019-10-30 02:29:49.000 |

I want to merge all these rows into one like so:

| id | totalDowntime | ocurrences | date                    | totalDowntime2 | ocurrences2 | date2                   | totalDowntime3 | ocurrences3 | date3                   |
|----|---------------|------------|-------------------------|----------------|-------------|-------------------------|----------------|-------------|-------------------------|
| 52 | 11909         | 19         | 2019-10-28 14:01:58.000 | 3980           | 12          | 2019-10-29 23:25:00.000 | 158            | 2           | 2019-10-30 02:29:49.000 |

Notes:

Unknown number of columns
Chronological order

I've already looked at these questions (1, 2) but I'm still confused with it.

How can I achieve this?

If the number of columns is unknown, you cannot do it in standard SQL, you need dynamic SQL, which is far more complex. — GMB, Nov 04 '19 at 12:19
How about: writing a query that can handle, say, up to 10 records per id? That might just be enough for your use case. — GMB, Nov 04 '19 at 12:22
@GMB the duplicate worked fine but I always need to specify the columns, how would you do with dynamic SQL? — CaldeiraG, Nov 04 '19 at 13:11
Where will you display the results of this query? It's usually far better to handle these sorts of tasks in your reporting tool (Excel, SSRS, Power BI etc.), — BarneyL, Nov 04 '19 at 14:32
@BarneyL you're right. I eventually found a better way to achieve this. Thanks for your advice. — CaldeiraG, Nov 04 '19 at 14:41

Combine multiple rows to one row with multiple columns

0 Answers0