1 Reply Latest reply: Oct 7, 2016 9:52 AM by Bill Markham RSS

    Merge or Concatenate Rows

    Joey Lutes

      I have built the following table:





      Left(TestId, Len(Trim(TestId))-3) as TestIdKey,

      Value as ZeroMax

      Resident [datatable]

      Where AVal=1, BVal='House' ;





      Left(TestId, Len(Trim(TestId))-3) as TestIdKey,

      Value as FiveMin

      Resident [datatable]

      where AVal=2, BVal='Condo';


      It produces this:


      TestID           TestIDKey          ZeroMax             FiveMin

      123-aa                123                  12                      

      123-ab                123                                              7

      124-aa                124                   6

      124-ab                124                                              2


      I'm looking for the following:

      For any given TestIDKey,

      ZeroMax - FiveMin as Delta


      I've tried to create a table without TestID in it, only using TestIDKey, but it keeps the multiple rows no matter what I try.

      I'm trying to get it to do this, but it keeps showing 2 rows for each TestIDKey


      TestIDKey           ZeroMax               FiveMin          Delta

      123                         12                           7                5

      124                         6                             2                4



      Ultimately, I need that to link to TestID so that:


      TestID          TestIDKey           ZeroMax                  FiveMin          Delta

      123-aa              123                     12                                               5

      123-ab              123                                                    7                  5

      124-aa              124                      6                                               4

      124-ab              124                                                    2                 4