SQL Server array_agg (master - detail)

Question

How can I convert this PostgreSQL code to SQL Server ?

select 
    countries.title, 
    (select array_to_json(array_agg(row_to_json(t))) 
     from postcodes t 
     where t.country_id = countries.id) as codes 
from countries

My initial problem is that I need to select complete master table and with each row all details.

Countries:

id    title
1     SLO
2     AUT

PostCodes:

id  country_id  code    title
1   1           1000    Lj
2   1           2000    Mb
3   2           22180   Vi
4   2           22484   De

Desired result:

1  SLO  1000;Lj|2000;MB
2  AUT  22180;Vi|22484;De

Not:

1  SLO  1000  Lj
1  SLO  2000  Mb
2  AUT  22180 Vi
2  AUT  22484 De

The best solution would be using FOR JSON, but unfortunately I need support for 2008 or at least 2012.

With left join all master data are duplicated for detail count, but I do not want to do this. Even worse it would be to select all countries and then call select on post_codes for every country in for loop.

SQL Server 2008 is no longer supported. 2012 is the earliest supported version, although 2016 is the obvious choice since you get Columnstore indexes, compression, in-memory tables, partitioning even in SQL Server Express and LocalDB (with SP1). It may be cheaper to upgrade than code JSON generation in T-SQL — Panagiotis Kanavos, Dec 14 '16 at 09:40
I know but convincing 500 clients that they should bought newer SQL is mission impossible. They want that our product would work with 2005. Insane if you ask me. :) — Makla, Dec 14 '16 at 09:43
Even though their data could fit in the free SQL Server Express version? Anyway, such conversions are far easier and safer on the *client*. String manipulation in the database is expensive. Besides, what are you going to do if some data requires escaping? — Panagiotis Kanavos, Dec 14 '16 at 09:46

score 6 · Accepted Answer · answered Dec 14 '16 at 08:33

select countries.title, 
       STUFF((select '|' + t.code + ';' + t.title
                from postcodes t 
                where t.country_id = countries.id
                FOR XML PATH('')
              ),1,1,'') as codes 
     from countries

-- CAST t.code to VARCHAR if it's Number

score 0 · Answer 2 · answered Dec 14 '16 at 08:44

try this:

Select Main.COUNTRY_ID,c.title,Left(Main.POSTCODES,Len(Main.POSTCODES)-1) As "POSTCODES"
From
    (
        Select distinct ST2.COUNTRY_ID, 
            (
                Select ST1.CODE+';'+ST1.TITLE + '|' AS [text()]
                From dbo.POSTCODES ST1
                Where ST1.COUNTRY_ID = ST2.COUNTRY_ID
                ORDER BY ST1.COUNTRY_ID
                For XML PATH ('')
            ) [POSTCODES]
        From dbo.POSTCODES ST2
    ) [Main]

    inner join countries c on c.id=main.country_id

score 0 · Answer 3 · edited May 23 '17 at 11:45

Using XML PATH for concatenation can increase the complexity of your code. It's better to implement a CLR aggregation function. Then, you can do the following:

SELECT C.[id]
      ,C.[title]
      ,REPLACE([dbo].[Concatenate] (P.[code] + ';' + P.[title]), ',', '|')
FROM @Countries C
INNER JOIN @PostCodes P
    ON C.[id] = p.[country_id]
GROUP BY C.[id]
        ,C.[title];

You can create your own version of the concatenate aggregate - you can specify the delimiter, the order, etc. I can show you examples if you want.

DECLARE @Countries TABLE
(
    [id] TINYINT
   ,[title] VARCHAR(12)
);

INSERT INTO @Countries ([id], [title])
VALUES (1, 'SLO')
      ,(2, 'AUT');

DECLARE @PostCodes TABLE
(
    [id] TINYINT
   ,[country_id] TINYINT
   ,[code] VARCHAR(12)
   ,[title] VARCHAR(12)
);

INSERT INTO @PostCodes ([id], [country_id], [code], [title])
VALUES (1, 1, 1000, 'Lj')
      ,(2, 1, 2000, 'Mb')
      ,(3, 2, 22180, 'Vi')
      ,(4, 2, 22484, 'De');

SELECT C.[id]
      ,C.[title]
      ,REPLACE([dbo].[Concatenate] (P.[code] + ';' + P.[title]), ',', '|')
FROM @Countries C
INNER JOIN @PostCodes P
    ON C.[id] = p.[country_id]
GROUP BY C.[id]
        ,C.[title];

SQL Server array_agg (master - detail)

3 Answers3