SQL query to combine duplicate rows with comma separated values in SQL Server

priyajsr
 
on Jun 11, 2018 12:49 AM
2472 Views

Following is my table data       

EnrollNumber   InputMode Date

7024                     0        2018-01-06 05:41:52.000

7024                     0        2018-01-06 05:41:57.000

7024                      1        2018-01-06 18:20:01.000

7024                      0        2018-02-06 17:37:50.000

7024                      0        2018-03-06 06:08:51.000

7024                      1        2018-03-06 06:09:08.000

7024                       1       2018-03-06 13:59:15.000

7024                       0        2018-04-06 17:45:20.000

7024                       1         2018-05-06 06:08:09.000

7024                       0        2018-05-06 17:45:50.000

7024                       1        2018-06-06 06:09:06.000

Following is the result which i am getting from query

SELECT DISTINCT EnrollNumber,
(SELECT  min(CONVERT(VARCHAR(10), Date, 121)) FROM Log lg1 WHERE Log.EnrollNumber=lg1.EnrollNumber AND InputMode=0  AND CONVERT(date,lg1.Date)=CONVERT(date,Log.Date)) as Date,
(SELECT  STUFF((SELECT ','+ CONVERT(VARCHAR(8), lg2.Date,114) FROM Log lg2 WHERE lg2.EnrollNumber = Log.EnrollNumber AND InputMode=0 AND CONVERT(date,lg2.Date)=CONVERT(date,Log.Date) FOR XML PATH('')),1,1,'')) as InTime,
(SELECT  STUFF((SELECT ','+ CONVERT(VARCHAR(8), lg3.Date,114) FROM Log lg3 WHERE lg3.EnrollNumber = Log.EnrollNumber AND InputMode=1 AND CONVERT(date,lg3.Date)=CONVERT(date,Log.Date) FOR XML PATH('')),1,1,'')) as OutTime  
FROM Log where EnrollNumber='7024' AND InputMode=0 order by [Date]

EnrollNumber   Date             InTime                     OutTime

7024             2018-01-06  05:41:52,05:41:57    18:20:01

7024             2018-02-06   17:37:50                  NULL

7024             2018-03-06   06:08:51                 06:09:08,13:59:15

7024             2018-04-06   17:45:20                 NULL

7024             2018-05-06   17:45:50                 06:08:09

As we see,I am not getting 2018-06-06 data, which input mode is only 1, so what i have to modify in query.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
nagaraju60
 
on Jun 12, 2018 12:59 AM
on Jun 12, 2018 01:02 AM

Dear @priyajsr,

Please refer below query , you will get answer. make sure no other conditions.

SELECT DISTINCT 
(SELECT distinct EnrollNumber FROM Log lg4  WHERE  Log.EnrollNumber=lg4.EnrollNumber AND lg4.InputMode=Log.InputMode AND CONVERT(date,lg4.Date)=CONVERT(date,Log.Date)) as EnrollNumber,
(SELECT  min(CONVERT(VARCHAR(10), Date, 121)) FROM Log lg1 WHERE Log.EnrollNumber=lg1.EnrollNumber AND lg1.InputMode=Log.InputMode AND CONVERT(date,lg1.Date)=CONVERT(date,Log.Date)) as Date,
(SELECT  STUFF((SELECT ','+ CONVERT(VARCHAR(8), lg2.Date,114) FROM Log lg2 WHERE lg2.EnrollNumber = Log.EnrollNumber AND InputMode=0 AND CONVERT(date,lg2.Date)=CONVERT(date,Log.Date) FOR XML PATH('')),1,1,'')) as InTime,
(SELECT  STUFF((SELECT ','+ CONVERT(VARCHAR(8), lg3.Date,114) FROM Log lg3 WHERE lg3.EnrollNumber = Log.EnrollNumber AND InputMode=1 AND CONVERT(date,lg3.Date)=CONVERT(date,Log.Date) FOR XML PATH('')),1,1,'')) as OutTime 
FROM Log where EnrollNumber='7024'  order by [Date]