locked
SQL Qry row transpose RRS feed

  • 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
    Tuesday, August 4, 2020 12:39 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 5, 2020 3:11 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 5, 2020 3:11 AM
  • User-582711651 posted

    Dear yij sun, 

    Excellent, thanks a lot. 

    Wednesday, August 5, 2020 11:40 AM