|number |line |partNum |phoneNum     |qty_SN |qty_Property |qty_Name |qty_Time
------------------------------------------------------------------------------
|87     |1    |55G5    |555-789-7512 |00123  |Local        |Owner    |05:22
|87     |     |        |             |14988  |Local        |Seller   |10:44
|87     |     |        |             |521    |Remote       |Owner    |01:05
|87     |     |        |             |50697  |Local        |Seller   |11:41
|87     |     |        |             |2359   |Remote       |Seller   |04:45

|number |line |partNum |phoneNum     |qty_SN                     |qty_Property                    |qty_Name                         |qty_Time
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|87     |1    |55G5    |555-789-7512 |00123,14988,521,50697,2359 |Local,Local,Remote,Local,Remote |Owner,Seller,Owner,Seller,Seller |05:22,10:44,01:05,11:41,04:45

;WITH TheDATA AS (
   SELECT
      BL.number                                  AS 'number',
      BL.line                                    AS 'line',
      BL.partNumber                              AS 'partNum',
      BL.phoneNumber                             AS 'phoneNum',
      L.qtySN                                    AS 'qty_SN',
      I.qtyProperty                              AS 'qty_Property',
      I.qtyName                                  AS 'qty_Name',
      I.qtyTime                                  AS 'qty_Time',
      ROW_NUMBER() Over (
         PARTITION BY
             BL.number
         ORDER BY
             BL.number
      )                                          AS 'RowNo'
   FROM
      BList                                      AS BL
   INNER JOIN Location                           AS L
      ON BL.ID                                    = L.Route
   INNER JOIN Inventory                          AS I
      ON L.ID                                     = I.prodID
SELECT
   number                                        AS 'number',
   IIF(RowNo = 1, CONVERT(varchar, line), '')    AS 'line',
   IIF(RowNo = 1, partNumber, '')                AS 'partNum',
   IIF(RowNo = 1, phoneNumber, '')               AS 'phoneNum',
   qty_SN                                        AS 'qty_SN',
   qty_Property                                  AS 'qty_Property',
   qty_Name                                      AS 'qty_Name',
   qty_Time                                      AS 'qty_Time'
FROM
   TheData

;WITH CTE(number ,line ,partNum ,phoneNum     ,qty_SN ,qty_Property ,qty_Name ,qty_Time)
AS
(
SELECT 87     ,1    ,  '55G5'    ,'555-789-7512'    ,00123  ,'Local'        ,'Owner'    ,'05:22' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,14988  ,'Local'        ,'Seller'   ,'10:44' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,521    ,'Remote'       ,'Owner'    ,'01:05' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,50697  ,'Local'        ,'Seller'   ,'11:41' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,2359   ,'Remote'       ,'Seller'   ,'04:45'
)
SELECT * FROM
(
SELECT DISTINCT  number, line,partNum ,phoneNum,
        STUFF((SELECT ', '+CAST(qty_SN AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_SN,
        STUFF((SELECT ', '+CAST(qty_Property AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_Property,
        STUFF((SELECT ', '+CAST(qty_Name AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_Name,
        STUFF((SELECT ', '+CAST(qty_Time AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_Time
FROM CTE
)dt
WHERE line IS NOT NULL AND  partNum IS  NOT NULL AND phoneNum IS NOT NULL