locked
how to convert from rows to different different column based on the Date according to paricular patient id RRS feed

  • Question

  • User1878568433 posted

    Suppose we have 3 Different Date according to one patient ID.

    How to show in table have 2 columns

    Please help me soon ..this is urgent

    My data like this below :-

    Row_Num | PatientID | MemberLastName | MemberFirstName |    MemberDOB |            |      DOS                                   Procedure_Code
    1 |                  345551     VEGATENEZACA          FREDY              2012-03-21 00:00:00.000     2017-08-21 00:00:00.000          90700
    2 |                  345551 VEGATENEZACA              FREDY              2012-03-21 00:00:00.000     2016-03-21 00:00:00.000          90700

    But I want like this  Below:

    PatientID | MemberLastName | MemberFirstName |    MemberDOB |            |      DOS_1                                 DOS_2                             Procedure_Code 

    345551     VEGATENEZACA          FREDY               2012-03-21 00:00:00.000     2017-08-21 00:00:00.000        2016-03-21 00:00:00.000   90700

    My Query Below:

    Drop Table #ss
    create table #ss
    (
    Row_Num int,
    PatientID int,
    MemberLastName Varchar(100),
    MemberFirstName Varchar(100),
    MemberDOB DateTime,
    Claim_Number Varchar(50),
    DOS DateTime,
    Procedure_Code varchar(50)
    )
    Insert Into #ss
    select DISTINCT ROW_NUMBER() OVER(ORDER BY DOS Desc) AS Row_Num,c.PatientID,tp.VC_Last_Name,
    tp.VC_First_Name,tp.SDT_DOB,c.Claim_Number,c.DOS,tto.Procedure_Code
    from tbl_claims c inner join tbl_Transactions_Opteli tto on c.Claim_Number=tto.Claim_Number
    Inner join tbl_Patient tp on c.PatientID=tp.N_Patient_ID
    where c.ClientID=114 and tto.Transaction_Type='A' and tto.Procedure_Code='90700'

    select * from #ss

    Friday, December 14, 2018 8:02 AM

All replies

  • User-1174608757 posted

    HI sripadasatpathy

    According to your description,I have made a sample here. you can use inner join to take one table as two,then you could choose different data in different rows in the same table.Here is the code ,I hope it will help you.

    patient database:

    sql code:

    select p1.PatientID,p1.MemberName,p1.Dos as Dos_1 ,p2.Dos as Dos_2,p1.Procedure_Code
    from patient as p1
    join patient as p2
    on  p1.PatientID=P2.PatientID
    where p1.Row_NUM=1 and p2.Row_NUM=2
    

    It shows:

      

    Monday, December 17, 2018 6:17 AM