PostgreSql ARRAY_AGG AND UNNEST alternatives in SQL Server

May 3, 2023

I have the following query in PostgreSql and I need to convert it to SQL Server:

SELECT
    InvoiceId,
    MatchIssue,
    ids,
    UNNEST(ids) AS Id,
    UNNEST(amounts) AS Amount,
    UNNEST(counterpartyExternalIds) AS counterpartyExternalId,
    UNNEST(companyExternalIds) AS companyExternalId
FROM 
    (SELECT 
         InvoiceId, 
         (CASE WHEN COUNT(distinct InvoiceDate) = 1 THEN '' ELSE 'MATCH ISSUE IN DATES ' END || 
            CASE WHEN COUNT(distinct Currency) = 1 THEN '' ELSE 'MATCH ISSUE IN CURRENCIES ' END || 
            CASE WHEN COUNT (distinct DueDate) = 1 THEN '' ELSE 'MATCH ISSUE IN DUE DATES ' END || 
            CASE WHEN SUM(amount) = 0 THEN '' ELSE 'MATCH ISSUE IN AMOUNTS ' END) AS MatchIssue,
         array_agg(id) ids,
         array_agg(amount) amounts,
         array_agg(counterpartyExternalId) counterpartyExternalIds,
         array_agg(companyExternalId) companyExternalIds
     FROM 
         Invoices
     WHERE 
         invoicestatus = 0 OR invoicestatus = 3
     GROUP BY 
         InvoiceId
     HAVING 
         (NOT COUNT(InvoiceId) = 1) = true) sub

What I have tried is the following:

SELECT
    InvoiceId,
    MatchIssue,
    Ids,
    ids_split.[value] AS Id,
    CAST([Amounts].value AS NUMERIC) AS Amount,
    CounterpartyExternalIds.[value] AS CounterpartyExternalId,
    CompanyExternalIds.[value] AS CompanyExternalId
FROM 
    (SELECT 
         InvoiceId, 
         (CASE WHEN COUNT(DISTINCT InvoiceDate) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN DATES ' 
          END 
          + CASE WHEN COUNT(DISTINCT Currency) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN CURRENCIES ' 
            END 
          + CASE WHEN COUNT (DISTINCT DueDate) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN DUE DATES ' 
            END 
          + CASE WHEN SUM(amount) = 0 THEN '' 
                    ELSE 'MATCH ISSUE IN AMOUNTS ' 
            END) AS MatchIssue,
         STRING_AGG(Id, ',') AS Ids,
         STRING_AGG(Amount, ',') AS Amounts,
         STRING_AGG(CounterpartyExternalId, ',') AS CounterpartyExternalIds,
         STRING_AGG(CompanyExternalId, ',') AS CompanyExternalIds
     FROM 
         Invoices
     WHERE 
         InvoiceStatus = 0 OR InvoiceStatus = 3
     GROUP BY 
         InvoiceId
     HAVING 
         COUNT(InvoiceId) <> 1) AS sub
CROSS APPLY 
    STRING_SPLIT(Ids, ',') AS ids_split
CROSS APPLY 
    STRING_SPLIT(Amounts, ',') AS Amounts
CROSS APPLY 
    STRING_SPLIT(CounterpartyExternalIds, ',') AS CounterpartyExternalIds
CROSS APPLY 
    STRING_SPLIT(CompanyExternalIds, ',') AS CompanyExternalIds
WHERE 
    LEN(ids_split.[value]) > 0

The issue is that in PostgreSql the UNNEST function retrieves the related value of column, however in SQL Server the data are being duplicated.

Below are screenshots from both PostgreSql and SQL Server:

PostgreSql:

SQL Server:

>Solution :

While it is possible to do what you have done from Postgres in SQL Server (using a combination of STRING_AGG and OPENJSON or STRING_SPLIT), it seems you can actually just use window functions here.

There is no COUNT(DISTINCT for window functions, but in your particular case you can just compare MIN with MAX.

SELECT
  i.InvoiceId,
  i.MatchIssue,
  i.Id,
  i.Amount,
  i.counterpartyExternalId,
  i.companyExternalId
FROM (
    SELECT i.*,
      CONCAT_WS(' ',
        CASE WHEN MIN(i.InvoiceDate) OVER (PARTITION BY i.InvoiceId)
               <> MAX(i.InvoiceDate) OVER (PARTITION BY i.InvoiceId)
             THEN 'MATCH ISSUE IN DATES' END,
        CASE WHEN MIN(i.Currency) OVER (PARTITION BY i.InvoiceId)
               <> MAX(i.Currency) OVER (PARTITION BY i.InvoiceId)
             THEN 'MATCH ISSUE IN CURRENCIES' END,
        CASE WHEN MIN(i.DueDate) OVER (PARTITION BY i.InvoiceId)
               <> MAX(i.DueDate) OVER (PARTITION BY i.InvoiceId)
             THEN 'MATCH ISSUE IN DUE DATES' END,
        CASE WHEN SUM(i.amount) OVER (PARTITION BY i.InvoiceId) <> 0
             THEN 'MATCH ISSUE IN AMOUNTS' END
      ) AS MatchIssue
    FROM Invoices i
    WHERE i.invoicestatus IN (0, 3)
) i;

CONCAT_WS joins text with a separator.

In SQL Server 2022 and Azure, you can use the new WINDOW clause to avoid repeating the PARTITION BY