STUFF SQL

by lokointhehouse2

HTML

SELECT DISTINCT
            af.ad_id, af.ad_date, af.retailer_name, af.retailer_ID, af.medium_type_name, af.medium_name, af.segment_name, 
            pf.mfg_name, af.market_id, af.market_name, 
            --pf.client_report_group_id, 
            --pf.client_report_group_name,
            --pf.brand_id, 
            --pf.brand_name, 
            pf.variety_name, pf.ad_model_year, pf.ad_price, pf.ad_price_msrp, 
            pf.ad_price_apr, pf.ad_deal, pf.ad_purchase_code, pf.ad_cc_reduct, 
            pf.dealer_rebate_amount [DealerDollarOff], pf.brand_rebate_amount [BrandDollarOff],
         --   (select BRAND_NAME FROM #AdFlat WHERE AD_ID = af.ad_id) as S
		stuff (
		(select DISTINCT ',' + pf.BRAND_NAME from #AdFlat a JOIN #PriceFlat pf ON pf.ad_id = af.ad_id  where a.AD_ID = af.ad_id
		FOR XML PATH ('')), 1, 1, ''
		) AS BRANDS 

   
      FROM 
            #AdFlat af
            JOIN #PriceFlat pf ON pf.ad_id = af.ad_id    
      ORDER BY 
            af.retailer_name, af.retailer_ID, af.ad_date