Post reply

Dynamic Pivot using XML as column list

  • June 20, 2017 at 8:32 am

    #343292

    I am trying to pull out a list of products from a product table, in the form of a comma separated and square bracket enclosed list. I then pass the list in to a pivot table so that in the future if any products are added or removed from the product table, the corresponding column is added/removed from the result set.

    The code I am using to extract the list of products in a single record is:
    SELECT @ColumnsForPivot = CAST((SELECT STUFF((SELECT ',[' + CAST(fldProductCode AS VARCHAR(255)) + ']' FROM tlkp_Product ORDER BY fldProductCode FOR XML PATH('')),1,1,'')) AS VARCHAR(MAX))

    This returns data as follows (which is not what I am after):
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I need the data without special characters converted to entities, like this:
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I have added the square brackets because when the product names are used as columns in my pivot, some will contain spaces or start with a number.

    Is there an easier way to maintain these 'special/reserved characters' or do I need to use the REPLACE command?

  • June 20, 2017 at 8:48 am

    #1947387

    Try this

    SELECT @ColumnsForPivot = CAST((SELECT STUFF((SELECT ',[' + CAST(fldProductCode AS VARCHAR(255)) + ']'
               FROM tlkp_Product
                                    ORDER BY fldProductCode FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(MAX)'),1,1,'')) AS VARCHAR(MAX))

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • June 20, 2017 at 8:55 am

    #1947388

    r.gall - Tuesday, June 20, 2017 8:32 AM

    I am trying to pull out a list of products from a product table, in the form of a comma separated and square bracket enclosed list. I then pass the list in to a pivot table so that in the future if any products are added or removed from the product table, the corresponding column is added/removed from the result set.

    The code I am using to extract the list of products in a single record is:
    SELECT @ColumnsForPivot = CAST((SELECT STUFF((SELECT ',[' + CAST(fldProductCode AS VARCHAR(255)) + ']' FROM tlkp_Product ORDER BY fldProductCode FOR XML PATH('')),1,1,'')) AS VARCHAR(MAX))

    This returns data as follows (which is not what I am after):
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I need the data without special characters converted to entities, like this:
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I have added the square brackets because when the product names are used as columns in my pivot, some will contain spaces or start with a number.

    Is there an easier way to maintain these 'special/reserved characters' or do I need to use the REPLACE command?

    There's an easier way than replace. You just need to add some options to the XML.

    CREATE TABLE #tlkp_Product(
      fldProductCode varchar(255))
    INSERT INTO #tlkp_Product VALUES('32BAG'),('AB-WSSS'),('AF(H/L)1'),('SPEC&MORT'),('USForm 68');

    DECLARE @ColumnsForPivot varchar(max);
    SELECT @ColumnsForPivot = STUFF((SELECT ',' + QUOTENAME( CAST(fldProductCode AS VARCHAR(255)))
               FROM #tlkp_Product
               ORDER BY fldProductCode FOR XML PATH(''),TYPE).value('./text()[1]', 'varchar(max)'),1,1,'');

    SELECT @ColumnsForPivot;
    GO
    DROP TABLE #tlkp_Product;

    I also used the QUOTENAME function to prevent errors if you have square brackets in your column names.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 20, 2017 at 8:56 am

    #1947389

    Mark Cowne - Tuesday, June 20, 2017 8:48 AM

    Try this

    SELECT @ColumnsForPivot = CAST((SELECT STUFF((SELECT ',[' + CAST(fldProductCode AS VARCHAR(255)) + ']'
               FROM tlkp_Product
                                    ORDER BY fldProductCode FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(MAX)'),1,1,'')) AS VARCHAR(MAX))

    You're a legend!  That worked for me!

  • June 20, 2017 at 8:58 am

    #1947390

    Luis Cazares - Tuesday, June 20, 2017 8:55 AM

    r.gall - Tuesday, June 20, 2017 8:32 AM

    I am trying to pull out a list of products from a product table, in the form of a comma separated and square bracket enclosed list. I then pass the list in to a pivot table so that in the future if any products are added or removed from the product table, the corresponding column is added/removed from the result set.

    The code I am using to extract the list of products in a single record is:
    SELECT @ColumnsForPivot = CAST((SELECT STUFF((SELECT ',[' + CAST(fldProductCode AS VARCHAR(255)) + ']' FROM tlkp_Product ORDER BY fldProductCode FOR XML PATH('')),1,1,'')) AS VARCHAR(MAX))

    This returns data as follows (which is not what I am after):
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I need the data without special characters converted to entities, like this:
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I have added the square brackets because when the product names are used as columns in my pivot, some will contain spaces or start with a number.

    Is there an easier way to maintain these 'special/reserved characters' or do I need to use the REPLACE command?

    There's an easier way than replace. You just need to add some options to the XML.

    CREATE TABLE #tlkp_Product(
      fldProductCode varchar(255))
    INSERT INTO #tlkp_Product VALUES('32BAG'),('AB-WSSS'),('AF(H/L)1'),('SPEC&MORT'),('USForm 68');

    DECLARE @ColumnsForPivot varchar(max);
    SELECT @ColumnsForPivot = STUFF((SELECT ',' + QUOTENAME( CAST(fldProductCode AS VARCHAR(255)))
               FROM #tlkp_Product
               ORDER BY fldProductCode FOR XML PATH(''),TYPE).value('./text()[1]', 'varchar(max)'),1,1,'');

    SELECT @ColumnsForPivot;
    GO
    DROP TABLE #tlkp_Product;

    I also used the QUOTENAME function to prevent errors if you have square brackets in your column names.

    +1 on the use of QUOTENAME

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • June 20, 2017 at 9:01 am

    #1947391

    Luis Cazares - Tuesday, June 20, 2017 8:55 AM

    r.gall - Tuesday, June 20, 2017 8:32 AM

    I am trying to pull out a list of products from a product table, in the form of a comma separated and square bracket enclosed list. I then pass the list in to a pivot table so that in the future if any products are added or removed from the product table, the corresponding column is added/removed from the result set.

    The code I am using to extract the list of products in a single record is:
    SELECT @ColumnsForPivot = CAST((SELECT STUFF((SELECT ',[' + CAST(fldProductCode AS VARCHAR(255)) + ']' FROM tlkp_Product ORDER BY fldProductCode FOR XML PATH('')),1,1,'')) AS VARCHAR(MAX))

    This returns data as follows (which is not what I am after):
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I need the data without special characters converted to entities, like this:
    [32BAG],[AB-WSSS],[AF(H/L)1],[SPEC&MORT],[USForm 68]

    I have added the square brackets because when the product names are used as columns in my pivot, some will contain spaces or start with a number.

    Is there an easier way to maintain these 'special/reserved characters' or do I need to use the REPLACE command?

    There's an easier way than replace. You just need to add some options to the XML.

    CREATE TABLE #tlkp_Product(
      fldProductCode varchar(255))
    INSERT INTO #tlkp_Product VALUES('32BAG'),('AB-WSSS'),('AF(H/L)1'),('SPEC&MORT'),('USForm 68');

    DECLARE @ColumnsForPivot varchar(max);
    SELECT @ColumnsForPivot = STUFF((SELECT ',' + QUOTENAME( CAST(fldProductCode AS VARCHAR(255)))
               FROM #tlkp_Product
               ORDER BY fldProductCode FOR XML PATH(''),TYPE).value('./text()[1]', 'varchar(max)'),1,1,'');

    SELECT @ColumnsForPivot;
    GO
    DROP TABLE #tlkp_Product;

    I also used the QUOTENAME function to prevent errors if you have square brackets in your column names.

    It is a possibility that there could be square brackets in a name (or any character, as I made a type in my original post - the data type is NVARCHAR, not VARCHAR), so I'll add in the QUOTENAME bit too.

    Thanks for your input!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy