I am trying to reverse or somehow pivot appended rows from a table through an SQL Query. The following example illustrates the table structure I have

And I am trying to modify the query to pivot or group the rows equivalent to the following:

What would be a solution for this query?

I have tried pivoting the query like the following which according to my research should do what I am hoping for, but maybe I am missing something as it returns an error message below.

SELECT Facility, 
       Site, 
       SUBSTRING(Name,
                 CHARINDEX('_',Name) 1,
                 ( ((LEN(Name)) - CHARINDEX('_', REVERSE(Name))) 
                   - CHARINDEX('_',Name) )
       ) AS Panel, 
       dateadd(hh,-7,TimestampUTC) as TimeStamp, 
       ActualValue 
FROM PSS_KPIHistory 
WHERE Name LIKE '%PercentLoopsInNormal' 
PIVOT(ActualValue for Panel in(select distinct Panel from PSS_KPIHistory))

The above query returns columns Facility, Site, an extracted string from the column "Name" stored as new Column "Panel", a Timestamp and the Value (ActualValue). I am returning everything from the table that contains "PercentLoopsInNormal" in the "Name" Column. This returns the following error:

Message=Incorrect syntax near the keyword 'PIVOT'. Incorrect syntax near ')'.

CodePudding user response：

your data doesn't fit your wanted result, changing it you can do

you would need a dynamic approach to the problem

CREATE TABLE table1
    ([Timestamp] DATE, [ID] varchar(1), [Value] int)
;
    
INSERT INTO table1
    ([Timestamp], [ID], [Value])
VALUES
    ('2023-01-18', 'A', 10),
    ('2023-01-19', 'A', 15),
    ('2023-01-20', 'A', 20),
    ('2023-01-18', 'B', 10),
    ('2023-01-19', 'B', 15),
    ('2023-01-20', 'B', 20),
    ('2023-01-18', 'C', 10),
    ('2023-01-19', 'C', 15),
    ('2023-01-20', 'C', 20)
;

9 rows affected

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

-- select the Distinct ID
SELECT 
    @columns =QUOTENAME( [ID])   ','
FROM 
    (SELECT DISTINCT [ID] FROM table1) t1
ORDER BY 
    [ID];

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
       [Timestamp], [ID], [Value]
    FROM 
        table1
) t 
PIVOT(
    MAX([Value])
    FOR [ID] IN ('  @columns  ')
) AS pivot_table;';
--SELECT @sql
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

fiddle

CodePudding user response：

Here's the traditional pivot approach:

• Step 1: identifying values from a single column that should be split in different columns (your discriminatory values are 'A', 'B' and 'C')
• Step 2: aggregating on the field for which specific value you want one record only in the output, in this case [Timestamp]

SELECT [Timestamp],
       MAX(CASE WHEN [ID] = 'A' THEN [Value] END) AS A,
       MAX(CASE WHEN [ID] = 'B' THEN [Value] END) AS B,
       MAX(CASE WHEN [ID] = 'C' THEN [Value] END) AS C
FROM tab
GROUP BY [Timestamp]

Typically Step1 requires the use of a window function, but in this case your [ID] field is ready to be used by Step2.

Check the demo here.