SQL Server - Comma separated result

Question

I am using a query below:

SELECT DISTINCT 
    DTL.DTL_ITEM_CRC DTL_ITEM_CRC, 
    PWK.PWK_PROMN_WEEK_KEY PWK_PROMN_WEEK_KEY, 
    HDR.HDR_VENDR_NBR HDR_VENDR_NBR, 
    HDR.HDR_VENDR_NAME HDR_VENDR_NAME 
FROM
    DBO.PROMWEEK PWK, DBO.BLTNHDR HDR, DBO.BLTNDET DTL, DBO.ITEMWHSE ITW
WHERE 
    DTL.DTL_BULTN_KEY = HDR.HDR_BULTN_KEY 
    AND HDR.HDR_PROMN_WEEK_KEY = PWK.PWK_PROMN_WEEK_KEY 
    AND HDR.HDR_BULTN_KEY = ITW.ITW_BULTN_KEY 
    AND DTL.DTL_ITEM_NBR = ITW.ITW_ITEM_NBR  
    AND DTL.DTL_ITEM_CRC IN ('2180198') 
    AND PWK.PWK_PROMN_WEEK_KEY IN ('31','32') 
    AND GETDATE() < PWK.PWK_START_DATE  
    AND DTL_ITEM_CRC != '0000000' 
    AND DTL.DTL_TBLD_CONUS_CNTRL > 0 
    AND DTL.DTL_TBLD_CONUS_EAST > 0 
    AND DTL.DTL_TBLD_CONUS_SOUTH > 0 
    AND DTL.DTL_TBLD_CONUS_WEST > 0
    AND DTL.DTL_SPECL_SELL > 0

The result of this query is

DTL_ITEM_CRC    HDR_VENDR_NBR   HDR_VENDR_NAME  PROMO_WEEK
2180198 60531000    US COTTON LLC           31 
2180198 60531000    US COTTON LLC           32

I want to get the following:

DTL_ITEM_CRC    HDR_VENDR_NBR   HDR_VENDR_NAME  PROMO_WEEK
2180198 60531000    US COTTON LLC           31 ,32

I am trying the following but did not work:

SELECT A.DTL_ITEM_CRC ,A.HDR_VENDR_NBR, A.HDR_VENDR_NAME,STUFF((SELECT distinct ','+ PWK1.PWK_PROMN_WEEK_KEY
                                                                  FROM DBO.PROMWEEK PWK1,DBO.BLTNHDR HDR1, DBO.BLTNDET DTL1, DBO.ITEMWHSE ITW1
                                                                  WHERE A.DTL_ITEM_CRC = DTL1.DTL_ITEM_CRC 
                                                                  AND A.HDR_VENDR_NBR = HDR1.HDR_VENDR_NBR 
                                                                  AND A.HDR_VENDR_NAME = HDR1.HDR_VENDR_NAME
                                                                  AND A.PWK_PROMN_WEEK_KEY = PWK1.pwk_promn_week_key
                                                                  GROUP BY PWK1.PWK_PROMN_WEEK_KEY
                                                                  FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') PROMO_WEEK
FROM  
(SELECT DISTINCT DTL.DTL_ITEM_CRC DTL_ITEM_CRC, PWK.PWK_PROMN_WEEK_KEY PWK_PROMN_WEEK_KEY, HDR.HDR_VENDR_NBR HDR_VENDR_NBR, HDR.HDR_VENDR_NAME HDR_VENDR_NAME 
FROM DBO.PROMWEEK PWK, DBO.BLTNHDR HDR, DBO.BLTNDET DTL, DBO.ITEMWHSE ITW
WHERE DTL.DTL_BULTN_KEY = HDR.HDR_BULTN_KEY AND HDR.HDR_PROMN_WEEK_KEY = PWK.PWK_PROMN_WEEK_KEY 
AND HDR.HDR_BULTN_KEY = ITW.ITW_BULTN_KEY AND DTL.DTL_ITEM_NBR = ITW.ITW_ITEM_NBR  
and DTL.DTL_ITEM_CRC in ('2180198') 
AND PWK.PWK_PROMN_WEEK_KEY in ('31','32') AND GETDATE() < PWK.PWK_START_DATE  AND DTL_ITEM_CRC != '0000000' 
AND DTL.DTL_TBLD_CONUS_CNTRL > 0 AND DTL.DTL_TBLD_CONUS_EAST > 0 AND DTL.DTL_TBLD_CONUS_SOUTH > 0 AND DTL.DTL_TBLD_CONUS_WEST > 0 AND DTL.DTL_SPECL_SELL > 0 ) A

can you please help?

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Jul 09 '18 at 19:55
@Mulliganaceous I disagree, it's very clear what he's asking. He wants to end up with a column containing comma-delimited values, instead of a new row for each of the values in that column. — Nick Coad, Jul 10 '18 at 00:02
Please don't make more work for people by vandalizing your posts. By posting on the Stack Exchange (SE) network, you've granted a non-revocable right, under the [CC BY-SA 3.0 license](//creativecommons.org/licenses/by-sa/3.0), for SE to distribute that content (i.e. regardless of your future choices). By SE policy, the non-vandalized version of the post is the one which is distributed. Thus, any vandalism will be reverted. — Makyen, Jul 10 '18 at 00:03
@Nick Coad The post was already fixed and the formatting was done soon after I posted this. When I saw this in review it was made vandalized and unclear. Sorry about that. — Ṃųỻịgǻňạcểơửṩ, Jul 10 '18 at 00:19

score 0 · Answer 1 · answered Jul 09 '18 at 20:22

There's a string-agg function in newer versions of SQL Server, but if you're on an older version you can use XML functions to get your desired output. The query looks horrible, but this should work (haven't tested it without sample data, so you'll probably need to tweak it):

SELECT DISTINCT 
    DTL.DTL_ITEM_CRC
    , PROMO_WEEK =
        (
        SELECT  STUFF( 
                (SELECT ', ' + PWK_PROMN_WEEK_KEY
                FROM 
                    DBO.PROMWEEK PWK
                    JOIN DBO.BLTNHDR HDR
                        ON HDR.HDR_PROMN_WEEK_KEY = PWK.PWK_PROMN_WEEK_KEY 
                    JOIN DBO.BLTNDET DTL
                        ON DTL.DTL_BULTN_KEY = HDR.HDR_BULTN_KEY 
                    JOIN DBO.ITEMWHSE ITW
                        ON DTL.DTL_ITEM_NBR = ITW.ITW_ITEM_NBR  
                        AND HDR.HDR_BULTN_KEY = ITW.ITW_BULTN_KEY 
                WHERE 
                    DTL.DTL_ITEM_CRC IN ('2180198') 
                    AND PWK.PWK_PROMN_WEEK_KEY IN ('31','32') 
                    AND GETDATE() < PWK.PWK_START_DATE  
                    AND DTL_ITEM_CRC != '0000000' 
                    AND DTL.DTL_TBLD_CONUS_CNTRL > 0 
                    AND DTL.DTL_TBLD_CONUS_EAST > 0 
                    AND DTL.DTL_TBLD_CONUS_SOUTH > 0 
                    AND DTL.DTL_TBLD_CONUS_WEST > 0
                    AND DTL.DTL_SPECL_SELL > 0
                  ORDER BY  PWK_PROMN_WEEK_KEY
                    FOR   XML PATH('') ,
                              ROOT('MyString') ,
                              TYPE 
                 ).value('/MyString[1]', 'varchar(max)'), 1, 2, ''
            )
        )
    , HDR.HDR_VENDR_NBR 
    , HDR.HDR_VENDR_NAME
FROM DBO.PROMWEEK PWK
    JOIN DBO.BLTNHDR HDR
        ON HDR.HDR_PROMN_WEEK_KEY = PWK.PWK_PROMN_WEEK_KEY 
    JOIN DBO.BLTNDET DTL
        ON DTL.DTL_BULTN_KEY = HDR.HDR_BULTN_KEY 
    JOIN DBO.ITEMWHSE ITW
        ON DTL.DTL_ITEM_NBR = ITW.ITW_ITEM_NBR  
        AND HDR.HDR_BULTN_KEY = ITW.ITW_BULTN_KEY 
WHERE 
    DTL.DTL_ITEM_CRC IN ('2180198') 
    AND PWK.PWK_PROMN_WEEK_KEY IN ('31','32') 
    AND GETDATE() < PWK.PWK_START_DATE  
    AND DTL_ITEM_CRC != '0000000' 
    AND DTL.DTL_TBLD_CONUS_CNTRL > 0 
    AND DTL.DTL_TBLD_CONUS_EAST > 0 
    AND DTL.DTL_TBLD_CONUS_SOUTH > 0 
    AND DTL.DTL_TBLD_CONUS_WEST > 0
    AND DTL.DTL_SPECL_SELL > 0

SQL Server - Comma separated result

1 Answers1