Looking to make multiple rows into single row-CodePudding

I am trying to pull together a report with dates test was completed for individuals persons.

Right now my query is,

Select 
    PI.Lastname, PI.firstname, PI.personid,
    case PT.title 
       when 'Test1' then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
    End As test,
    case PF.title 
      when 'Test2' then CONVERT(varchar(max), cast(PT.Datecompleted as Date),0) 
    End As test2,
    case  PF.title 
        when 'Test3' 
        then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
    End As test3,
    case  PF.title 
        when 'Test4' 
        then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0)  
    End As test4,
    case  PF.title 
        when 'Test5' 
       then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
    End As test5,
    case  PF.title
        when 'Test6' 
        then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0)  
    End As test6,
    case  PF.title 
      when 'Test7' 
      then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
    End As test7,
    case  PF.title when 'Test8' 
      then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
    End As test8
from 
    Person_Info PI
inner join 
    person_Test PT on PT.personid=PI.personId

My query results currently look like this:

Lastname firstname  PersonId    test    test2   test3   test4   test5   test6   test7   test8
Ronald  Jim 1000000 Aug 18 2022 NULL    NULL    NULL    NULL    NULL    NULL    NULL
Ronald  Jim 1000000 NULL    NULL    NULL    NULL    Aug 18 2022 NULL    NULL    NULL
Ronald  Jim 1000000 NULL    NULL    NULL    NULL    NULL    Aug 18 2022 NULL    NULL
Ronald  Jim 1000000 NULL    NULL    NULL    NULL    NULL    NULL    Aug 18 2022 NULL
Ronald  Jim 1000000 NULL    NULL    NULL    NULL    NULL    NULL    NULL    Aug 18 2022
Ronald  Jim 1000000 NULL    Aug 18 2022 NULL    NULL    NULL    NULL    NULL    NULL
Ronald  Jim 1000000 NULL    NULL    Aug 18 2022 NULL    NULL    NULL    NULL    NULL
Ronald  Jim 1000000 NULL    NULL    NULL    Aug 18 2022 NULL    NULL    NULL    NULL

Screenshot

But What I am looking to get is the following:

Lastname    firstname   PersonId    test    test2   test3   test4   test5   test6   test7   test8
Ronald  Jim 1000000 Aug 18 2022 Aug 18 2022 Aug 18 2022 Aug 18 2023 Aug 18 2024 Aug 18 2025 Aug 18 2026 Aug 18 2027

What I am looking to get

CodePudding user response：

Here are alternate 2 ways to do this. Example 1 does a sub-query for every test column, for every person. This will not perform well on a large dataset. Example 2 does one sub query for each test column, but does a LEFT OUTER JOIN to the person. This should perform better than Example 1.

Example 1:

SELECT DISTINCT
    [PI].LastName
    , [PI].firstName
    , [PI].personid
    , (
        SELECT *
        FROM person_Test as PT
        WHERE PT.personid=[PI].personid
            AND PT.title = 'Test1'
    ) as test1
    , (
        SELECT *
        FROM person_Test as PT
        WHERE PT.personid=[PI].personid
            AND PT.title = 'Test2'
    ) as test2
    , (
        SELECT *
        FROM person_Test as PT
        WHERE PT.personid=[PI].personid
            AND PT.title = 'Test3'
    ) as test3
FROM Person_Info as [PI]

Example 2:

SELECT DISTINCT
    [PI].LastName
    , [PI].firstName
    , [PI].personid
    , CONVERT(varchar(max), cast(PT1.Datecompleted  as Date),0) as test1
    , CONVERT(varchar(max), cast(PT2.Datecompleted  as Date),0) as test2
    , CONVERT(varchar(max), cast(PT3.Datecompleted  as Date),0) as test3
FROM Person_Info as [PI]
    LEFT OUTER JOIN person_Test as PT1
        ON PT1.personid = [PI].personid
        AND PT1.title = 'Test1'
    LEFT OUTER JOIN person_Test as PT2
        ON PT2.personid = [PI].personid
        AND PT2.title = 'Test1'
    LEFT OUTER JOIN person_Test as PT3
        ON PT3.personid = [PI].personid
        AND PT3.title = 'Test1'

CodePudding user response：

Simply adding a group by and an aggregation around your case statements will achieve your desired results:

Select PI.Lastname, PI.firstname, PI.personid,
max(case PT.title 
   when 'Test1' then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
End) As test,
max(case PF.title 
  when 'Test2' then CONVERT(varchar(max), cast(PT.Datecompleted as Date),0) 
End) As test2,
max(case  PF.title 
    when 'Test3' 
    then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
End) As test3,
case  PF.title 
    when 'Test4' 
    then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0)  
End As test4,
case  PF.title 
    when 'Test5' 
   then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
End As test5,
max(case  PF.title
    when 'Test6' 
    then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0)  
End) As test6,
max(case  PF.title 
  when 'Test7' 
  then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
End) As test7,
max(case  PF.title when 'Test8' 
  then CONVERT(varchar(max), cast(PT.Datecompleted  as Date),0) 
End) As test8
from Person_Info PI
inner join person_Test PT on PT.personid=PI.personId
GROUP BY PI.Lastname, PI.firstname, PI.personid