SQL Qry row transpose

Question

User-582711651 posted

Hi friends, 

Please help me how to do this 

Create Table #Tbl_Rework ([MLAI_ID] INT, [USER_ID] INT, [ReWork_Remarks] Varchar(100), [LastModifiedDate] Datetime)

Insert into #Tbl_Rework ([MLAI_ID],[USER_ID],[ReWork_Remarks],[LastModifiedDate])
Values(2535024,26029,'VCid and DOC interchange','2020/06/27 10:58:19')
Insert into #Tbl_Rework ([MLAI_ID],[USER_ID],[ReWork_Remarks],[LastModifiedDate])
Values(2535024,26067,'Doc missing','2020/06/27 11:30:19')
Insert into #Tbl_Rework ([MLAI_ID],[USER_ID],[ReWork_Remarks],[LastModifiedDate])
Values(2535024,26067,'Nominee proof mismatch and Ifsc code missing','2020/06/28  06:04:24')

Expected result;

MLAI_ID	Details
2535024	26029,VCid and DOC interchange,06/27/2020,10:19~ 26067,Doc missing06/27/2020,11:19~ 26067,Nominee proof mismatch and Ifsc code missing06/28/2020,06:24

Answer 1

User1535942433 posted

Hi ayyappan.CNN,

Accroding to your description,as far as I think,you could use STUFF.

Just like this:

SELECT MLAI_ID, Details= STUFF((SELECT '~' + convert(varchar(1000),USER_ID) +',' + ReWork_Remarks + ', ' +  convert(varchar,LastModifiedDate,101)+', ' + convert(varchar,LastModifiedDate,108)
    FROM #Tbl_Rework t2
     where t2.MLAI_ID = t1.MLAI_ID
     FOR XML PATH('')), 1, 1, '')
FROM #Tbl_Rework t1
GROUP BY MLAI_ID

Result:

However,it's strange for  your expected result without minutes.If you really need the same with your  expected result,you could use DATEPART to get the hours and seconds.

Just like this:

SELECT MLAI_ID, Details= STUFF((SELECT '~' + convert(varchar(1000),USER_ID) +',' + ReWork_Remarks + ', ' +  convert(varchar,LastModifiedDate,101)+', ' + Convert(varchar,DATEPART(HOUR,LastModifiedDate)) + ':' + Convert(varchar,DATEPART(SECOND, LastModifiedDate))
    FROM #Tbl_Rework t2
     where t2.MLAI_ID = t1.MLAI_ID
     FOR XML PATH('')), 1, 1, '')
FROM #Tbl_Rework t1
GROUP BY MLAI_ID

Result:

Best regards,

Yijing Sun

Answer 2

User-582711651 posted

Dear yij sun, 

Excellent, thanks a lot.