T-SQL Nested Subquery

Question

Not being a SQL expert, and also only being semi-competent in CTE, how can I code this statement use the resultset from the following subquery within the main query, as our SQL Server is 2000.

declare @subcategoryConcatenate varchar(3999)
set @subcategoryConcatenate = ''

select @subcategoryConcatenate = @subcategoryConcatenate + pumpCategoryName + ',' FROM
(SELECT
            SCD.PUMPCATEGORYNAME,
            SCD.ENGINECATEGORYNAME,
            SCD.DETAILEDDESCRIPTION
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 @subcategoryConcatenate

---

SELECT
            PHDR.CONNECTION_ID AS CONNECTION_ID,
            BHDR.OFFICE AS OFFICE,
            CMP.NAME AS DEPOT,
            CMP.ADDR1 AS DEPOT_ADDR1,
            CMP.ADDR2 AS DEPOT_ADDR2,
            CMP.CITY AS DEPOT_CITY,
            CMP.STATE AS DEPOT_STATE,
            CMP.ZIP AS DEPOT_ZIP,
            CMP.PHONENUM AS DEPOT_PHONE,
            CMP.FAXNUM AS DEPOT_FAX,
            ACT.NAME AS ACTIVITY,
            SAL.SALES_PERSON_NAME AS SALESPERSON,
            BHDR.DTICKET AS DELIV_TICKET_NUMBER,
            BHDR.PO_NUMBER,
            BHDR.CREATED AS CREATED_DATE,
                BHDR.DDATE AS ESTIMATED_START_DATE,
            BHDR.PROJ_STOP_DATE AS PROJECTED_STOP_DATE,
            CUR.ID,
            CUR.CODE,
            CUR.EXCHANGE_RATE,
            CST.TERMS,
            BHDR.ORDBY AS ORDERED_BY,
            PHDR.ORDERED_BY_CONTACT,
            BHDR.ACCT AS ACCOUNT,
            BHDR.NAME AS CUSTOMER,
            BHDR.ADDR1 AS CUST_ADDR1,
            BHDR.ADDR2 AS CUST_ADDR2,
            BHDR.CITY AS CUST_CITY,
            BHDR.STATE AS CUST_STATE,
            BHDR.ZIP AS CUST_ZIP,
            PHDR.SHIP_TO_NAME,
            PHDR.SHIP_TO_ADDR1,
            PHDR.SHIP_TO_ADDR2,
            PHDR.SHIP_TO_CITY,
            PHDR.SHIP_TO_STATE,
            PHDR.SHIP_TO_ZIP,
            PITM.PRINT_SEQUENCE,
            PITM.ITEM,
            PITM.SUBGROUP,
            PITM.DESCRIPTION,
            SCD.PUMPCATEGORYNAME,
            SCD.ENGINECATEGORYNAME,
            SCD.DETAILEDDESCRIPTION,
            PITM.QUANTITY,
            PITM.UNIT_OF_MEASURE,
            PITM.BILLING_LOGIC_TYPE,
            PITM.INVENTORY_TYPE,
            PITM.CHARGEABLE_DAYS,
            PITM.MINIMUM_CHARGE,
            PITM.WEEKLY_CHARGE,
            PITM.MONTHLY_CHARGE,
            PITM.UNINVOICED_NET,
            PITM.UNINVOICED_VAT
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
INNER JOIN COMPANY CMP ON BHDR.OFFICE = CMP.OFFICE
LEFT JOIN SUBCATEGORYDESCRIPTION SCD ON PITM.ITEM = SCD.PUMPCATEGORY
INNER JOIN ACTIVITIES ACT ON BHDR.ACTIVITY_ID = ACT.ID
INNER JOIN SALES_PERSON SAL ON BHDR.SALES_PERSON = SAL.SALES_PERSON
INNER JOIN CUSTOMERS CST ON BHDR.ACCT = CST.CUSTNUM
INNER JOIN CURRENCY CUR ON CST.CURRENCY_ID = CUR.ID
ORDER BY
            BHDR.DTICKET,
            PITM.PRINT_SEQUENCE
ASC

Answer 1

SQL Server 2000 doesn't support CTEs. Your options are to either make a view out of the subquery if it's used a lot, or to do an inline view:

select
  .. stuff..
from
  table1 t1
  join table2 t2 on ...stuff...
  join (
    select
       ...
    from
       ...
    where
       ...
  ) inline on ... stuff ...
where
  ....

Answer 2

You need a user-defined function.
From the looks of it, each PRTTICKITEM can have more than one PUMPCATEGORY?
(The question needs to better explain the desired results.)

In that case, your UDF would look something like this:

CREATE FUNCTION  GetPumpCategoriesByItem (@ItemID int)

    RETURNS varchar (8000)
AS
BEGIN
    DECLARE
        @CategoryList       varchar (8000)
    SET @CategoryList       = NULL  -- MUST be null to avoid leading comma.

    SELECT
        @CategoryList       =  COALESCE (@CategoryList + ', ', '') + SCD.PUMPCATEGORYNAME
    FROM
        SUBCATEGORYDESCRIPTION SCD
    WHERE
        SCD.PUMPCATEGORY    = @ItemID
    ORDER BY
        SCD.PUMPCATEGORYNAME

    RETURN @CategoryList
END

.
To use it would be something like this:

SELECT
    PITM.ITEM,
    dbo.GetPumpCategoriesByItem (PITM.ITEM),
    ... ...
FROM 
    ... ...
INNER JOIN PRTTICKITEM PITM ON ... ...
... ...