SQL Transpose the data

Question

I have one dataset as follows:

HID     HDate               Comments
60038A  1/5/2018 5:39:48    TEST
60038A  1/6/2018 9:02:03    sample
60038A  1/8/2018 9:23:27    another test
60038A  1/6/2018 10:33:00   delayed

data set tells about that for some ID column date wise comments are saved now what i want is ,the date column should display as columns with their respective comments.

I wrote pivot query for the same as follows:

DECLARE @QUERY NVARCHAR(MAX), @Soucecolumn VARCHAR(MAX)
SET @Soucecolumn = STUFF((SELECT distinct ',[' + Convert(varchar,[HDate],120) + ']' FROM TRCK_Comments FOR XML PATH('')),1,1,'')
SET @QUERY = 'SELECT HID, ' + @Soucecolumn + ' FROM TRCK_Comments PIVOT (MAX(Comments) FOR [HDate] IN (' + @Soucecolumn + ')) AS pvt'
exec sp_executesql  @QUERY

but above query giving me result with multiple rows with same ID, with NULL values I want single row same ID with whatever comments available in that particular date column,

Actual result that i am getting

    HID 2018-01-04 11:26:05    2018-01-04 11:26:16  2018-01-04 11:26:27 2018-01-04 18:26:46
    1   this is sample comment  NULL                 NULL               NULL
    1   NULL                    this is comment      NULL               NULL
    2   NULL                    NULL                this is sample      NULL
    2   NULL                    NULL                NULL               nfdjfdfddf

Expected Reult

    HID 2018-01-04 11:26:05    2018-01-04 11:26:16  2018-01-04 11:26:27 2018-01-04 18:26:46
    1   this is sample comment  this is comment     NULL                NULL

    2   NULL                    NULL                this is sample      nfdjfdfddf

Here is my actual dataset

ID  HID HDate               Comments
1   1   2018-01-04 11:26:05 this is sample comment
2   1   2018-01-04 11:26:16 this is sample comment this is sample comment
3   2   2018-01-04 11:26:27 this is sample comment this is sample comment this is sample comment
4   2   2018-01-04 18:26:46 nfdjfdfddf

want to do group by HID (not the ID which is identity type)

Answer 1

With the sample data you have provided:

CREATE TABLE #DataSource
(
    [ID] TINYINT 
   ,[HID] VARCHAR(12)
   ,[HDate] DATETIME2
   ,[Comments] VARCHAR(128)
);

INSERT INTO #DataSource ([ID], [HID], [HDate], [Comments])
VALUES ('1', '1', '2018-01-04 11:26:05', 'this is sample comment')
      ,('2', '1', '2018-01-04 11:26:16', 'this is sample comment this is sample comment')
      ,('3', '2', '2018-01-04 11:26:27', 'this is sample comment this is sample comment this is sample comment')
      ,('4', '2', '2018-01-04 18:26:46', 'nfdjfdfddf');

DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);



SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                    SELECT ',[' + CONVERT(VARCHAR(19), [HDate], 120) + ']'
                                    FROM #DataSource
                                    GROUP BY [HDate]
                                    ORDER BY [HDate]
                                    FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );


SET @DynammicTSQLStatement = N'
SELECT *
FROM
(
    SELECT [HID], [HDate], [Comments]
    FROM #DataSource
) DS
PIVOT
(
    MAX([Comments]) FOR [HDate] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynammicTSQLStatement;

DROP TABLE #DataSource;