I have some rates for resources for all countries
The rows will be Resource IDs
Columns should be Country Codes
Challenge here, I cannot sort the Country Codes in ASC
It would be so grateful if you could help me on this.

When I query, I get the list of country codes, but not sorted. i.e., USA,BRA,ARG etc. But the expected result should be ARG,BRA,USA in columns of the pivot.

Here is my code:

DECLARE @idList nvarchar(MAX)

SELECT
    @idList = COALESCE(@idList   ',', '')   CountryCodeISO3
FROM
    (
        SELECT
            DISTINCT CountryCodeISO3
        FROM
            Published.RateCardsValues
        WHERE
            CardID = 55
    ) AS SRC 
    
DECLARE @sqlToRun nvarchar(MAX)

SET
    @sqlToRun = '
SELECT * 
FROM ( 
    SELECT 
       [ResourceCode]
      ,[TITLES]
      ,[MostRepresentativeTitle]
      ,[ABBR_RES_DESC]
      ,[TypicalJobGrade]
      ,[BidGridResourceCode]
      ,[OpUnit]
      ,[PSResType]
      ,[JobGradeORResCat]
      ,[CountryCodeISO3]
      --,[CurrencyCode]
      ,[RateValue]

    FROM 
        [Published].[RateCardsValues] rc
    WHERE 
        CardID = 55) As src
PIVOT ( 
    MAX(RateValue) FOR [CountryCodeISO3] IN ('   @idList   ')  
) AS pvt' 

EXEC (@sqlToRun)

CodePudding user response：

As you have discovered, PIVOT in T-SQL requires you to know at development time what the values will be that you will be pivoting on.

This is limiting, because if you want something like "retrieve data for all the countries where Condition X is true, then pivot on their IDs!", you have to resort to dynamic SQL to do it.

If Condition X is constant -- I'm guessing that belonging to CardID = 55 doesn't change often -- you can look up the values, and hardcode them in your code.

If the CardID you're looking up is always 55 and you have relatively few countries in that category, I'd actually advise doing that.

But if your conditions for picking countries can change, or the number of columns you want can vary -- something like "all the countries where there were sales of product Y, for month Z!" -- then you can't predict them, which means that the T-SQL PIVOT can't be set up (without dynamic SQL.)

In that case, I'd strongly suggest that you have whatever app you plan to use the data in do the pivoting, not T-SQL. (SSRS and Excel can both do it themselves, and code can be written to do it in .NET langauges.) T-SQL, as you have seen, does not lend itself to dynamic pivoting.

What you have will "work" in the sense that it will execute without errors, but there's another downside, in the next stage of your app: not only will the number of columns potentially change over time, the names of the columns will change, as countries move in and out of Card ID 55. That may cause problems for whatever app or destination you have in mind for this data.

So, my two suggestions would be: either hard-code your country codes, or have the next stage in your app (whatever executes the query) do the actual pivoting.

CodePudding user response：

You need to sort the columns while creating the dynamic SQL

Also:

• Do not use variable coalescing, use STRING_AGG or FOR XML instead
• Use QUOTENAME to escape the column names
• sp_executesql allows you to pass parameters to the dynamic query

DECLARE @idList nvarchar(MAX)

SELECT
    @idList = STRING_AGG(QUOTENAME(CountryCodeISO3), ',') WITHIN GROUP (ORDER BY CountryCodeISO3)
FROM
    (
        SELECT
            DISTINCT CountryCodeISO3
        FROM
            Published.RateCardsValues
        WHERE
            CardID = 55
    ) AS SRC;
    
DECLARE @sqlToRun nvarchar(MAX)

SET
    @sqlToRun = '
SELECT * 
FROM ( 
    SELECT 
       [ResourceCode]
      ,[TITLES]
      ,[MostRepresentativeTitle]
      ,[ABBR_RES_DESC]
      ,[TypicalJobGrade]
      ,[BidGridResourceCode]
      ,[OpUnit]
      ,[PSResType]
      ,[JobGradeORResCat]
      ,[CountryCodeISO3]
      --,[CurrencyCode]
      ,[RateValue]

    FROM 
        [Published].[RateCardsValues] rc
    WHERE 
        CardID = 55) As src
PIVOT ( 
    MAX(RateValue) FOR [CountryCodeISO3] IN ('   @idList   ')  
) AS pvt' 

EXEC sp_executesql @sqlToRun;