PIVOT without aggregate function ID as column names-CodePudding

I have a query

SELECT a.Name, b.Date FROM
Table1 a 
JOIN Table2 b on a.deviceID=b.id
where a.date > '2022-10-01'

Which gives me result

Name Date
A1   '2022-10-01 12:13'
A2   '2022-10-02 14:15'
A2   '2022-10-02 15:16'
A5   '2022-10-03 16:19'

etc.

The result I want to achieve is

A1                 A2                        A5 
'2022-10-01 12:13' '2022-10-02 14:15''2022-10-03 16:19' 
                    '2022-10-02 15:16'

The perfect result would be to receive only one date of each day. Can I do it with pivot?

CodePudding user response：

Pivoting cannot be done (in T-SQL) without aggregation. As for what you want to achieve, seems you need to PIVOT/conditionally aggregate on the value of Name and group on the value of a ROW_NUMBER.

I, personally, prefer using conditional aggregation over the restrictive PIVOT operator, but I have included examples of both:

USE Sandbox;
GO

CREATE TABLE #YourData (Name char(2),
                        Date datetime2(0));
GO
INSERT INTO #YourData (Name,
                       Date)
VALUES('A1','2022-10-01T12:13:00'),
      ('A2','2022-10-02T14:15:00'),
      ('A2','2022-10-02T15:16:00'),
      ('A5','2022-10-03T16:19:00');
GO

WITH RNs AS(
    SELECT [Name],
           [Date],
           ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Date]) AS RN
    FROM #YourData)
SELECT MAX(CASE [Name] WHEN 'A1' THEN [Date] END) AS A1,
       MAX(CASE [Name] WHEN 'A2' THEN [Date] END) AS A2,
       MAX(CASE [Name] WHEN 'A5' THEN [Date] END) AS A5
FROM RNs
GROUP BY RN;
GO

SELECT P.A1,
       P.A2,
       P.A5
FROM (SELECT [Name],
             [Date],
             ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Date]) AS RN
     FROM #YourData)YD
PIVOT(MAX([date])
      FOR [Name] IN (A1,A2,A5))P;

GO
DROP TABLE #YourData;

CodePudding user response：

If you want to pivot but keep all your related values in the same field rather than creating fake rows, you can use STRING_AGG. You can replace the "," separator with a CHAR(10), or perhaps an HTML to add a line break. You won't see it in SSMS but depending on your front end, that would render the two items, one under each other.

DROP TABLE IF EXISTS #YourData
CREATE TABLE #YourData (Name char(2),
                        Date datetime2(0));
GO
INSERT INTO #YourData (Name,
                       Date)
VALUES('A1','2022-10-01T12:13:00'),
      ('A2','2022-10-02T14:15:00'),
      ('A2','2022-10-02T15:16:00'),
      ('A5','2022-10-03T16:19:00');
GO


DECLARE @Seperator VARCHAR(10) = ' , '

SELECT 
 [A1] , [A2], [A3], [A4], [A5]
FROM  
(
  SELECT name, STRING_AGG(date,@Seperator) AS Dates
    FROM #YourData
    GROUP BY Name
) AS SourceTable  
PIVOT  
(  
  MAX(Dates)  
  FOR Name IN ([A1], [A2], [A3], [A4], [A5])  
) AS PivotTable;