Combine Multiple records per Foreign Key

Question

I have a somewhat tricky table structure that was inherited from way legacy.

I have a table with about 4 columns that matter.

 DayNight    Cust_Code    Name     Phone     Counter
   D          ABC0111    Marty    aaaaa        1 
   D          ABC0111    John     bbbbb        2 
   D          ABC0111    Beth     ccccc        3 
   N          ABC0111    Sue      ddddd        1 
   N          ABC0111    Mary     eeeee        2 

I need to combine these 5 records into one row with the following stucture.

CustCode,     Day1,      Day2,      Day3,      Night1,      Night2,      Night3
ABC0111,   Marty aaaaa, John bbbbb, Beth ccccc, Sue ddddd , Mary eeeee,    null or ''

What I have tried

SELECT DISTINCT
  x.NAME,
  x.DAYNIGHT,
  x.PHONE,
  x.COUNTER,
   cp.NAME,
   cp.DAYNIGHT,
   cp.COUNTER,
   cp.PHONE,
   cp.POSITION
        FROM (
               SELECT *
               from table1 where
               table1.DAYNIGHT LIKE 'N'
             ) x
     join  table1 t1 on t1.CUST_CODE = x.CUST_CODE 
     where cp.DAYNIGHT LIKE 'D'

Answer 1

I would be inclined to do this using conditional aggregation:

select CustCode,
       max(case when DayNight = 'D' and Counter = 1 then Name + ' ' + Phone end) as Day1,
       max(case when DayNight = 'D' and Counter = 2 then Name + ' ' + Phone end) as Day2,
       max(case when DayNight = 'D' and Counter = 3 then Name + ' ' + Phone end) as Day3,
       max(case when DayNight = 'N' and Counter = 1 then Name + ' ' + Phone end) as Night1,
       max(case when DayNight = 'N' and Counter = 2 then Name + ' ' + Phone end) as Night2,
       max(case when DayNight = 'N' and Counter = 3 then Name + ' ' + Phone end) as Night3
from table1
group by CustCode;