locked
Join to Tables and display a single row for each ID in Gridview. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User810354248 posted

    Hi Good Evening

    In my asp.net+VB+SQL  web i have following two table (Main and emp_rmk) tables.

    Main

    MID  EmpNo   Name    DOB                  Gender       Status       Remarks

    1       123         Baiju      10/07/70           Male         Active         Frequent absent

    2       124         Raju      2 0/07/1980       Male         Active         Absconding

    3        126         Sanath   08/06/2012      Male         Active         Good Performer

    Emp_Rmk

    RmkID  empno           Track_Records

    1           124                Poor Performer

    2           123                 Leave extended wef 01 jan 20

    3           123                 Previous project incomplete

    I want data in grid view like this

    MID  EmpNo   Name    DOB                  Gender       Status       Remarks                         Track_record

    1       123         Baiju      10/07/70           Male         Active         Frequent absent            Previous project incomplete, Leave extended wef 01 jan 20

    2       124         Raju      2 0/07/1980       Male         Active         Absconding                  Poor Performer

    3        126         Sanath   08/06/2012      Male         Active         Good Performer

    I tried like this

    SELECT * FROM [Main] left join emp_rmk ON main.empno = emp_rmk.empno

    but if there is any track record then multiple rows are being created for a single employee

    Sunday, August 23, 2020 3:40 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User2103319870 posted

    You can try with below query

    Select m.*,
STUFF((
      SELECT ',' + er.TrackRecords
     FROM Emp_RMK er
      WHERE er.EmpNo = m.EmpNo
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from Main as m

    Sample Demo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 23, 2020 11:12 PM