Dynamic Pivot without aggregate in Excel

Question

I know this has been asked multiple times before, but it always use Sum or Max or something or count. I need to transform exactly rows to columns.. Data as follows:

ID  User    DateTime
--------------------------------
2   UserA   2018-04-01  22:45:35
2   UserA   2018-04-01  13:50:40
2   UserA   2018-04-02  05:56:38
2   UserA   2018-04-02  14:19:44
2   UserA   2018-04-02  14:23:13
2   UserA   2018-04-03  05:55:32
2   UserA   2018-04-03  05:58:33
2   UserA   2018-04-03  14:34:32
2   UserA   2018-04-05  13:08:37
3   UserB   2018-04-01  13:50:35
3   UserB   2018-04-01  22:45:27
3   UserB   2018-04-02  05:51:17
3   UserB   2018-04-02  14:24:00
3   UserB   2018-04-03  05:54:43
3   UserB   2018-04-03  14:35:21
3   UserB   2018-04-04  13:09:59

Now I need it to go like this in Microsoft Excel.

ID  User    2018.04.01  2018.04.02  2018.04.03  2018.04.04  2018.04.05
-----------------------------------------------------------------------
2   UserA   13:50:40    05:56:38    05:55:32                13:08:37
2   UserA   22:45:35    14:19:44    05:58:33            
2   UserA               14:23:13    14:34:32            
3   UserB   13:50:35    05:51:17    05:54:43    13:09:59    
3   UserB   22:45:27    14:24:00    14:35:21

I have been scratching my head over and to internet, but everyone uses Pivot Table to make this. I've tried all kind of tricks in Pivot Table, but can't seem to separate the values. The only available option is "Count".

I tried using following SQL Statement to pivot date and retrieve to excel.

declare @cols as nvarchar(max), @query as nvarchar(max);
declare @startDate nvarchar(max) = '1 apr 2018';
declare @endDate nvarchar(max) = '30 apr 2018';

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Tgl) FROM f_Filtered_Check(@startdate, @enddate) GROUP BY Tgl ORDER BY Tgl FOR XML Path(''), TYPE).value('.' , 'NVARCHAR(MAX)') ,1,1,'');

set @query = '
SELECT * FROM f_Filtered_Check(''' + @startdate + ''', ''' + @enddate + ''')
pivot (
    max(checktime)
    for Tgl in (' + @cols + ')
) pvt
ORDER BY UserID, SwapID';
execute(@query)

But I can't seem to enter @startDate and @endDate parameter using SQL window in Excel.

Thank you for help.

EDIT: To make things clear, my goal is to get that second table in Excel, while the first table is coming from SQL Server. I gather, that it can be done in 2 ways.

Using SQL statement above, but I don't know how to put @startDate and @endDate parameter to that statement from Excel. That '1 Apr 2018' and '30 Apr 2018' should be dynamic, and can be set by user before excel runs that query.
Retrieve and pivot data in Excel. But Excel refused to output multiple data in pivot, and the only accessible function is count.

Please pardon my English.

hkravitz hkravitz · Accepted Answer · 2018-04-09T07:44:30

I assume your function generates a list of dates so I made a small change without using the function, of course, you can incorporate it in the following solution:

    SET NOCOUNT ON 
    IF OBJECT_ID ('tempdb..##T') IS NOT NULL DROP TABLE ##T
    DECLARE @cols NVARCHAR(MAX) = ''
    DECLARE @Pivot NVARCHAR(MAX) 
    DECLARE @Start NVARCHAR(30)= '2018-04-01'
    DECLARE @End NVARCHAR(30) = '2018-04-05'
    ;WITH T (ID , [User]  ,  [DateTime]) AS 
    (


    SELECT 2  , 'UserA' ,  CAST('2018-04-01  22:45:35' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-01  13:50:40' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-02  05:56:38' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-02  14:19:44' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-02  14:23:13' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-03  05:55:32' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-03  05:58:33' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-03  14:34:32' AS DATETIME) UNION ALL
    SELECT 2  , 'UserA' ,  CAST('2018-04-05  13:08:37' AS DATETIME) UNION ALL
    SELECT 3  , 'UserB' ,  CAST('2018-04-01  13:50:35' AS DATETIME) UNION ALL
    SELECT 3  , 'UserB' ,  CAST('2018-04-01  22:45:27' AS DATETIME) UNION ALL
    SELECT 3  , 'UserB' ,  CAST('2018-04-02  05:51:17' AS DATETIME) UNION ALL
    SELECT 3  , 'UserB' ,  CAST('2018-04-02  14:24:00' AS DATETIME) UNION ALL
    SELECT 3  , 'UserB' ,  CAST('2018-04-03  05:54:43' AS DATETIME) UNION ALL
    SELECT 3  , 'UserB' ,  CAST('2018-04-03  14:35:21' AS DATETIME) UNION ALL
    SELECT 3  , 'UserB' ,  CAST('2018-04-04  13:09:59' AS DATETIME) 

    )

    SELECT *
    INTO ##T 
    FROM T

    SELECT @cols += ',' + QUOTENAME(T.Dts)
    FROM 
    (
     SELECT  CAST(CAST([DateTime] AS DATE) AS  VARCHAR(10))
      FROM ##T
      WHERE [DateTime]>= @Start
      AND [DateTime] < DATEADD(DAY,1,@End)
      GROUP BY CAST(CAST([DateTime] AS DATE) AS  VARCHAR(10))
    ) T (Dts)

    SET @cols =  STUFF(@cols,1,1,'')



    SET @Pivot =
    'SELECT Id, [User], '+@cols+'
    FROM 
        (
        SELECT Id , [User] , CONVERT(VARCHAR(10),[Datetime], 120) AS Dt , CONVERT(VARCHAR(8),[Datetime], 108) AS [Time] , 
                ROW_NUMBER () OVER (PARTITION BY CONVERT(VARCHAR(10),[Datetime], 120) ORDER BY [DateTime]  ) seq
        FROM ##T
        WHERE [DateTime] >= '''+@Start+'''
        AND [DateTime] < '''+CONVERT(VARCHAR(10),DATEADD(DAY,1,CONVERT(DATE,@End)))+'''
        ) E
    PIVOT 
        (
        MAX([Time]) FOR dt IN ('+@cols+')
        ) q
    '
    EXEC sp_executesql @Pivot

Dynamic Pivot without aggregate in Excel

1 Answers