I have the following SQL query:
SELECT stock_items.stockcode,
x_apvma_product.pcode,
x_apvma_product.fpname,
x_apvma_product.sname,
x_apvma_host.hostdesc,
x_apvma_pest.pestdesc
FROM x_apvma_product
INNER JOIN stock_items
ON stock_items.x_apvma_product_no = x_apvma_product.pcode
INNER JOIN x_apvma_product_use
ON x_apvma_product_use.pcode = x_apvma_product.pcode
LEFT OUTER JOIN x_apvma_host
ON x_apvma_host.hostcode = x_apvma_product_use.hostcode
LEFT OUTER JOIN x_apvma_pest
ON x_apvma_pest.pestcode = x_apvma_product_use.pestcode
WHERE x_apvma_product.pcode IN (SELECT x_apvma_product_no
FROM stock_items)
AND stock_items.web_show = 'Y';
Which results in this (have chosen results from one stock item as there are over 74k rows to entire query):
stockcode pcode fpname sname hostdesc pestdesc
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD POTTING MIX, COMPOST, MANURE FUNGUS GNAT
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD POTTING MIX, COMPOST, MANURE SHORE FLY
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD AZALEA AZALEA LACE BUG
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD MARIGOLD YELLOW LEAFHOPPER
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD MARIGOLD LEAFHOPPER
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORCHID CITRUS MEALY BUG
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT CITRUS MEALY BUG
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD PALM CITRUS MEALY BUG
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD GERBERA GREENHOUSE WHITEFLY
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT GREENHOUSE WHITEFLY
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD FUCHSIA GREENHOUSE WHITEFLY
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD GERBERA SILVERLEAF WHITEFLY
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT SILVERLEAF WHITEFLY
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD FUCHSIA SILVERLEAF WHITEFLY
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT SCALE INSECTS
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ROSE ROSE APHID
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT PLAGUE THRIPS
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD MARIGOLD VEGETABLE LEAFHOPPER
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD LILLY PILLY PSYLLID OR LERP INSECT
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT GREEN COFFEE SCALE
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT PULVINARIA SCALE
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD AZALEA GREENHOUSE THRIP
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD ORNAMENTAL PLANT NIGRA SCALE
How do I change the query so I get 1 row for multiple x_apvma_host.hostdesc and have the multiple results of x_apvma_pest.pestdesc concatenated into one, separated by commas? This needs to be per stockcode of course.
Result should be:
Stockcode Pcode Fpname Sname Hostdesc pestconcat
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Azalea Azalea Lace Bug, Greenhouse Thrip
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Fuchsia Greenhouse Whitefly, Silverleaf Whitefly
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Gerbera Greenhouse Whitefly, Silverleaf Whitefly
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Lilly Pilly Psyllid Or Lerp Insect
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Marigold Leafhopper, Vegetable Leafhopper, Yellow Leafhopper
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Orchid Citrus Mealy Bug
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Ornamental Plant Citrus Mealy Bug, Green Coffee Scale, Greenhouse Whitefly, Nigra Scale, Plague Thrips, Pulvinaria Scale, Scale Insects, Silverleaf Whitefly
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Palm Citrus Mealy Bug
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Potting Mix, Compost, Manure Fungus Gnat, Shore Fly
9075 58558 CROWN 225SL SYSTEMIC INSECTICIDE EVERRIS AUSTRALIA PTY LTD Rose Rose Aphid
I'm aware that you can use STUFF and FOR XML PATH functions, however all the tutorials I have found are much simpler and don't involve joined tables, so far I haven't been able to work out how to use these in my situation and get the correct result.
Some test data here - http://sqlfiddle.com/#!9/b3f4e3/1/0
Please try to add SQL Fiddle Link with sample data.
You can try the following:
Edit: Query updated after sql fiddle added.
;WITH CTE AS
(
SELECT DISTINCT stock_items.stockcode,
x_apvma_product.pcode,
x_apvma_product.fpname,
x_apvma_product.sname,
x_apvma_host.hostdesc,
x_apvma_pest.pestdesc
FROM x_apvma_product
INNER JOIN stock_items
ON stock_items.x_apvma_product_no = x_apvma_product.pcode
INNER JOIN x_apvma_product_use
ON x_apvma_product_use.pcode = x_apvma_product.pcode
LEFT OUTER JOIN x_apvma_host
ON x_apvma_host.hostcode = x_apvma_product_use.hostcode
LEFT OUTER JOIN x_apvma_pest
ON x_apvma_pest.pestcode = x_apvma_product_use.pestcode
WHERE stock_items.web_show = 'Y'
)
SELECT DISTINCT
stockcode,
pcode,
fpname,
sname,
hostdesc,
pestdesc = STUFF((SELECT DISTINCT ','+pestdesc
FROM CTE c
WHERE c.hostdesc = c2.hostdesc
FOR XML PATH('')),1,1,'')
FROM CTE c2
Also there is no need of condition x_apvma_product.pcode IN (SELECT x_apvma_product_no FROM stock_items) because you already had JOIN between those tables.