T-SQL Nested Subquery

Question

I want to place this working code within a SQL Statement, OR do I need to perform a UDF.

The result set is a one line concatenation, and I want it to be place in every one of the overall result set lines.

----

MAIN QUERY

SELECT
    H.CONNECTION_ID,
   H.SEQUENTIAL_NO,
   H.INVOICE_NUMBER,
   H.INVOICE_DATE,
   H.LAST_INVOICE_NUMBER,
   H.LAST_INVOICE_DATE,
   CAST(CASE
      WHEN H.COLLECT_DEPOSIT = 1 THEN '-'
      ELSE CAST(H.PAYMENT_DUE_DATE AS NVARCHAR(20))
   END AS SMALLDATETIME) AS PAYMENT_DUE,
   H.JOB_NUMBER,
   H.CUST_JOB_NUMBER,
   HDR.SALES_PERSON,
   H.INSIDE_SALES_PERSON,
   H.IS_LAST_INVOICE,
   CASE
      WHEN H.COLLECT_DEPOSIT = 1 THEN 'CASH'
      ELSE H.PAYMENT_TERMS_DESCRIPTION
   END AS PAYMENT_TERMS,
   H.PRINTED,
   H.NOTES,
   CUR.ID,
   CUR.CODE,
   CASE CUR.CODE
      WHEN 'USD' THEN '001-106624-211'
      WHEN 'EUR' THEN '001-106624-101'
      WHEN 'GBP' THEN '001-106624-100'
      ELSE '001-106624-001'
   END AS BANK_ACCT,
   CUR.EXCHANGE_RATE,
   H.BILL_CONTACT,
   H.CUST_ACCOUNT, 
   H.CUST_NAME, 
   H.CUST_ADDR1, 
   H.CUST_ADDR2, 
   H.CUST_CITY, 
   H.CUST_STATE, 
   H.CUST_ZIP,
   H.CONTACT_PHONE_NUMBER,
   H.CONTACT_PHONE_NUMBER2,
   H.ORDERED_BY_CONTACT,
   H.SHIP_TO_NAME, 
   H.SHIP_TO_ADDR1, 
   H.SHIP_TO_ADDR2, 
   H.SHIP_TO_CITY, 
   H.SHIP_TO_STATE, 
   H.SHIP_TO_ZIP,
   H.SITE_PHONE_NUMBER,
   H.SITE_PHONE_NUMBER2,
   H.OFFICE_NAME,
   H.OFFICE_ADDR1,
   H.OFFICE_ADDR2,
   H.OFFICE_CITY,
   H.OFFICE_STATE,
   H.OFFICE_ZIP,
   H.OFFICE_PHONE_NUMBER,
   H.OFFICE_FAX_NUMBER,
   H.DELIVERY_TICKET_NUMBER,
   H.PO_NUMBER,
   H.DUMMY_INVOICE_TEXT,
   (SELECT MESSAGE FROM REPORT_MESSAGES WHERE CODE = 'INVOICE') ADVERT_MESSAGE,
   (SELECT MAX(DISCOUNT_PERCENTAGE) FROM PRTINVITEM I2 WHERE I2.CONNECTION_ID = H.CONNECTION_ID AND I2.INVOICE_NUMBER = H.INVOICE_NUMBER) AS MAX_DISCOUNT,
   I.ITEM,
   I.DESCRIPTION,
   I.QUANTITY,
   I.UNIT_OF_MEASURE,
   I.MINIMUM_CHARGE,
   I.WEEKLY_CHARGE,
   I.MONTHLY_CHARGE,
   I.START_OF_BILLING_PERIOD,
   I.END_OF_BILLING_PERIOD,
   I.DAYS_USED,
   I.WEEKS_USED,
   I.DISCOUNT_PERCENTAGE,
   I.TAX_CODE_FOR_ITEM,
   I.INVENTORY_TYPE,
   I.BILLING_LOGIC_TYPE,
   I.ACTUAL_WEEKLY_CHARGE_USED,
   I.DAYS_IN_ACTUAL_WEEKLY_CHARGE,
   II.CHARGEABLE_DAYS,
      II.CHARGEABLE_WEEKS,
   II.CHARGEABLE_MONTHS,
   II.FREE_DAYS_THIS_INVOICE,
   CNV.TOTAL_NET_VALUE,
   CNV.TOTAL_TAX_VALUE,
   CNV.TOTAL_GROSS_VALUE, 
   CNV.TOTAL_GROSS_VALUE_NS, 
   CNV.NET_LINE_VALUE,
   CMP.EMAIL_ADDRESS
FROM (PRTINVHDR H INNER JOIN PRTINVITEM I ON H.CONNECTION_ID = I.CONNECTION_ID AND H.INVOICE_NUMBER = I.INVOICE_NUMBER)
INNER JOIN INVOICEHDR HDR ON I.INVOICE_NUMBER = HDR.INVNO
INNER JOIN CUSTOMERS CST ON H.CUST_ACCOUNT = CST.CUSTNUM
INNER JOIN JOB JOB ON H.JOB_NUMBER = JOB.JOBNUM
INNER JOIN CURRENCY CUR ON HDR.CURRENCY_ID = CUR.ID
INNER JOIN VWCURRENCYCONVERSION CNV ON I.CONNECTION_ID = CNV.CONNECTION_ID AND I.INVC_UCOUNTER = CNV.INVC_UCOUNTER
INNER JOIN COMPANY CMP ON H.OFFICE_CODE = CMP.OFFICE
INNER JOIN INVOICEITEM II ON I.INVOICE_NUMBER = II.INVNO AND I.INVC_UCOUNTER = II.INVC_UCOUNTER
ORDER BY
   H.SEQUENTIAL_NO,
   I.PRINT_SEQUENCE
ASC

----

COALESCE QUERY

DECLARE 
   @DTICKET NVARCHAR(20),
   @PUMPCATEGORYNAME NVARCHAR(3999)

   SET @DTICKET = ''
   SET @PUMPCATEGORYNAME = NULL

(SELECT
   @DTICKET = DTICKET,
   @PUMPCATEGORYNAME = COALESCE(@PUMPCATEGORYNAME + ', ', '' ) + PUMPCATEGORYNAME
FROM (SELECT
      BHDR.DTICKET,
      SCD.PUMPCATEGORYNAME
   FROM PRTTICKHDR PHDR
   INNER JOIN BIDHDR BHDR ON PHDR.DELIV_TICKET_NUMBER = BHDR.DTICKET
   INNER JOIN PRTTICKITEM PITM ON PHDR.CONNECTION_ID = PITM.CONNECTION_ID AND PHDR.DELIV_TICKET_NUMBER = PITM.DELIV_TICKET_NUMBER
   LEFT JOIN SUBCATEGORYDESCRIPTION SCD ON PITM.ITEM = SCD.PUMPCATEGORY
   WHERE SCD.PUMPCATEGORYNAME IS NOT NULL)
SUBCATEGORYDESCRIPTION)

SELECT @DTICKET, @PUMPCATEGORYNAME

Answer 1

Not really sure what you are asking for but you can doing something along the lines of

Select col1 + ', ' + col2 + ', ' + col3 etc....