CODEWITHSUNDEEP
sqlsql-server-2005
Jack Cole
Jack Cole

Reputation: 1804

Joining tables without duplicating the row

I am currently joining 2 other tables when exporting 1, but this is causing rows to be duplicated. Instead of duplicating the row to match the value, is it possible to separate values of a specific row with commas?

Here is a sample of my table as it is now:

id,optioncatid,optionsdesc_sidenote,isproductcode,applytoproductcodes,stockstatus
"325","30","","BRB8PACK","00LDCLU131401C","17"
"325","30","","BRB8PACK","00LDDEV131401C","17"
"325","30","","BRB8PACK","00LDHEI131401C","17"
//etc

And this is what I would like it to be:

id,optioncatid,optionsdesc_sidenote,isproductcode,applytoproductcodes,stockstatus
"325","30","","BRB8PACK","00LDCLU131401C,00LDCLU131401C,00LDHEI131401C, etc...","17"
//etc

There can be thousands of values for applytoproductcodes, ballooning the file up to 200+MB when exporting as XML. This is obviously extremely bloated. My SQL query:

SELECT
    Options.ID,
    Options.OptionCatID,
    Options.optionsdesc_sidenote,
    Options.IsProductCode,
    Options_ApplyTo.ProductCode AS ApplyToProductCodes,
    Products.StockStatus AS StockStatus
FROM
    Options
JOIN Options_ApplyTo ON Options.ID = Options_ApplyTo.OptionID
JOIN Products ON Options.IsProductCode = Products.ProductCode
WHERE 
    Options.IsProductCode <> ''
ORDER BY
    Options.ID

Edit: Now I have done more research and modified my code to this:

SELECT
    Options.ID,
    Options.OptionCatID,
    Options.optionsdesc_sidenote,
    Options.IsProductCode,
    t.ProductCode AS ApplyToProductCodes,
    Products.StockStatus AS StockStatus
FROM
Options
LEFT JOIN 
    (
        select OptA.ProductCode as ProductCode, OptA.OptionID as OptionID
        from Options_ApplyTo AS OptA, Options
        WHERE  Options.ID = OptA.OptionID
        order by OptA.OptionID
        for xml path('')
    ) t
ON Options.ID = t.OptionID
LEFT JOIN Products 
ON Options.IsProductCode = Products.ProductCode
WHERE 
   Options.IsProductCode <> ''
ORDER BY
   Options.ID

But now I am getting the error No column was specified for column 1 of 't'.

Upvotes: 1

Views: 94

Answers (3)

dotjoe
dotjoe

Reputation: 26940

Just put that sucker right into the select. You cannot join on that as the for xml turns the result into a scalar value...not a table....

SELECT
    Options.ID,
    Options.OptionCatID,
    Options.optionsdesc_sidenote,
    Options.IsProductCode,
    (
        select OptA.ProductCode as ProductCode, OptA.OptionID as OptionID
        from Options_ApplyTo AS OptA
        WHERE  Options.ID = OptA.OptionID
        order by OptA.OptionID
        for xml path('')
    ) as ApplyToProductCodes,
    Products.StockStatus AS StockStatus
FROM
Options
LEFT JOIN Products 
ON Options.IsProductCode = Products.ProductCode
WHERE 
   Options.IsProductCode <> ''
ORDER BY
   Options.ID

Upvotes: 1

Low Chee Mun
Low Chee Mun

Reputation: 610

Sorry, maybe i am lack of experiences i feel that the information provided is not enough for me to write the test data for u, but i can give u an idea, use STUFF

DECLARE @t1 TABLE
(
id INT,
optioncatid INT,
optionsdesc_sidenote NVARCHAR(255),
isproductcode NVARCHAR(255),
applytoproductcodes INT
)


INSERT INTO @t1 VALUES 
(325,30,'BRB8PACK','00LDCLU131401C',17),
(325,30,'BRB8PACK','00LDCLU131401C',17),
(325,30,'BRB8PACK','00LDCLU131401C',17)



SELECT id,optioncatid,
STUFF((SELECT ','+optionsdesc_sidenote 
FROM @t1 WHERE id=325 FOR XML PATH('')) , 1 , 1  , '' ),
optionsdesc_sidenote,
isproductcode,
applytoproductcodes FROM @t1

Upvotes: 0

rKasun
rKasun

Reputation: 242

mysql GROUP_CONCAT function concatenates a column with comma character. Hence your query can be written as 

SELECT
    Options.ID,
    Options.OptionCatID,
    Options.optionsdesc_sidenote,
    Options.IsProductCode,
    t.pcodes AS ApplyToProductCodes,
    Products.StockStatus AS StockStatus
FROM
OPTIONS
JOIN 
(SELECT Options_ApplyTo.OptionID, GROUP_CONCAT(Options_ApplyTo.ProductCode) pcodes       
    FROM Options_ApplyTo GROUP BY Options_ApplyTo.OptionID) t
ON Options.ID = t.OptionID
JOIN Products 
ON Options.IsProductCode = Products.ProductCode
WHERE 
   Options.IsProductCode <> ''
ORDER BY
   Options.ID

Upvotes: 0

Related Questions