how to create different columns for the same timestamp in ms sql-CodePudding

If I run this SQL-query

 SELECT *
    FROM [Trend].[Details]
    WHERE [Timestamp] between '2023-01-27 08:00' and '2023-01-31'
        and [SignalId] in (74,63,119,80,101,99,109,124,120,117)
        order by [Timestamp]

It takes ms to complete.
If I run this SQL-query

 SELECT [Timestamp]
          ,[Value] '74'
          ,(SELECT [Value] FROM [Trend].[Details] d2 where d2.[Timestamp]=d1.[Timestamp] and d2.[SignalId]=63) '63'
          ,(SELECT [Value] FROM [Trend].[Details] d3 where d3.[Timestamp]=d1.[Timestamp] and d3.[SignalId]=119) '119'
          ,(SELECT [Value] FROM [Trend].[Details] d4 where d4.[Timestamp]=d1.[Timestamp] and d4.[SignalId]=80) '80'
          ,(SELECT [Value] FROM [Trend].[Details] d5 where d5.[Timestamp]=d1.[Timestamp] and d5.[SignalId]=101) '101'
          ,(SELECT [Value] FROM [Trend].[Details] d6 where d6.[Timestamp]=d1.[Timestamp] and d6.[SignalId]=99) '99'
          ,(SELECT [Value] FROM [Trend].[Details] d7 where d7.[Timestamp]=d1.[Timestamp] and d7.[SignalId]=109) '109'
          ,(SELECT [Value] FROM [Trend].[Details] d8 where d8.[Timestamp]=d1.[Timestamp] and d8.[SignalId]=124) '124'
          ,(SELECT [Value] FROM [Trend].[Details] d9 where d9.[Timestamp]=d1.[Timestamp] and d9.[SignalId]=120) '120'
          ,(SELECT [Value] FROM [Trend].[Details] d10 where d10.[Timestamp]=d1.[Timestamp] and d10.[SignalId]=117) '117'
      FROM [Trend].[Details] d1 with (nolock)
      where [SignalId]=74 and [Timestamp] between '2023-01-27 08:00' and '2023-01-31'
      order by [Timestamp]

It takes over 1 minute to complete.
It's the same information. Only pressented in differens columns and group by timestamp.

I want to pressent result as the second query but the query time as the first query. How do I write a good query that are fast?

CodePudding user response：

As mentioned in the comments, you would be better off pivoting your data here. Though you can use the PIVOT operator, I (and many others) find that conditional aggregation is "better" way, as it's far less restrictive on how it works:

SELECT [Timestamp],
       MAX(CASE SignalId WHEN 74  THEN [Value] END) AS [74],
       MAX(CASE SignalId WHEN 63  THEN [Value] END) AS [63],
       MAX(CASE SignalId WHEN 119 THEN [Value] END) AS [119],
       MAX(CASE SignalId WHEN 80  THEN [Value] END) AS [80],
       MAX(CASE SignalId WHEN 101 THEN [Value] END) AS [101],
       MAX(CASE SignalId WHEN 99  THEN [Value] END) AS [99],
       MAX(CASE SignalId WHEN 109 THEN [Value] END) AS [109],
       MAX(CASE SignalId WHEN 124 THEN [Value] END) AS [124],
       MAX(CASE SignalId WHEN 120 THEN [Value] END) AS [120],
       MAX(CASE SignalId WHEN 117 THEN [Value] END) AS [117]
FROM [Trend].[Details]
WHERE [Timestamp] BETWEEN '2023-01-27T08:00:00' AND '2023-01-31T00:00:00' --Switched to unambiguous literal date (and time) value
  AND [SignalId] IN (74,63,119,80,101,99,109,124,120,117)
GROUP BY [Timestamp]
ORDER BY [Timestamp];