FOR XML in SQL Server causing issue with spaces and :

Question

I have the sql table in the following format

   deal_key     std_industry_name            balance
   823155199    CORP -: Aerospace & Defense 10169652.43
   823155199    CORP -: Automotive          10155993.82
   823155199    CORP -: Beverage             14779701.43
   823155199    CORP -: Capital Equipment   15150957.89
   823155199    CORP -: Chemicals           14438116.97
   823155199    CORP -: Construction        4901198.24
   823155199    CORP -: Consumer goods       9647987.3

I need to transpose the table due to which i am doing a pivot on the table

And with the following query I am getting the xml output with some errors since the industry column contains spaces and the following character ':' in its data .

Is there a way in SQL Server to pivot on the column based on the data in table but still provide a alising for the column names or is there any other way to avoid this issue

  DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
                        '],[' + ltrim((std_industry_name))
                        FROM    [cs].[ma_deal_vintage_industry_statistics]
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @query =
'SELECT * FROM
(
    SELECT deal_key, std_industry_name,balance
    FROM [cs].[ma_deal_vintage_industry_statistics]
    where deal_key =''823155199''
)t
PIVOT (SUM(balance) FOR std_industry_name
IN ('+@years+')) AS pvt for xml auto'

EXECUTE (@query)

Answer 1

Looks like : in column header CORP -: Aerospace & Defense after pivoting is what making for Xml auto to fail.

Replace the : in pivot source query and also in column list. Try this.

   DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
                        '],[' + ltrim((replace(std_industry_name,':','')))
                        FROM    [cs].[ma_deal_vintage_industry_statistics]
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @query =
'SELECT * FROM
(
    SELECT deal_key, replace(std_industry_name,'':'','''') std_industry_name,balance
    FROM [cs].[ma_deal_vintage_industry_statistics]
    where deal_key =''823155199''
)t
PIVOT (SUM(balance) FOR std_industry_name
IN ('+@years+')) AS pvt for xml auto'

EXECUTE (@query)