Dynamic Pivot using XML as column list

• Rob

  Ten Centuries

  Points: 1035

  More actions

  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?

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  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

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  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

• Rob

  Ten Centuries

  Points: 1035

  More actions

  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!

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  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

• Rob

  Ten Centuries

  Points: 1035

  More actions

  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!