Say I have a table Account, and a table Tags. Tags contains all of the random string tags associated with an account. Tags have a TagType field, to indicate what type of tag it is (color, shape, size, etc).

I want to return aggregated lists of tags based on type on a per account basis. I've tried this:

SELECT a.accountid, 
       (SELECT STRING_AGG(t.TagName, ', ') WHERE t.TagType = 'color') AS Colors,
       (SELECT STRING_AGG(t.TagName, ', ') WHERE t.TagType = 'shape') AS Shapes,
       (SELECT STRING_AGG(t.TagName, ', ') WHERE t.TagType = 'size') AS Sizes
FROM dbo.account a
INNER JOIN dbo.tag t ON a.Id = t.AccountId
GROUP BY a.AccountID

Ideally, the results would be as such:

Row - AccountId -     Colors      -     Shapes     -     Sizes
1        10        Red, Blue         Round             NULL
2        11        Green             Rectangular       Small, Medium
etc...

However, this results in the following error:

Column 'dbo.tag.TagType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I group by TagType, each account is a separate row for each tag type. The whole point is to get one row per account with different tag types. How can I achieve that?

CodePudding user response：

Seems like you're looking for the WITHIN GROUP functionality of STRING_AGG (Transact-SQL), e.g.:

/*
 * Data setup...
 */
create table dbo.Account (
  AccountID int
);
insert dbo.Account (AccountID) values
  (10),
  (11);

create table dbo.Tag (
  AccountID int,
  TagName varchar(20),
  TagType varchar(20)
);
insert dbo.Tag (AccountID, TagName, TagType) values
  (10, 'Red', 'color'),
  (10, 'Blue', 'color'),
  (10, 'Round', 'shape'),
  (11, 'Green', 'color'),
  (11, 'Rectangular', 'shape'),
  (11, 'Small', 'size'),
  (11, 'Medium', 'size');

/*
 * Example query...
 */
SELECT a.AccountID, 
       STRING_AGG(case when t.TagType = 'color' then t.TagName end, ', ') WITHIN GROUP (order by t.TagName) AS Colors,
       STRING_AGG(case when t.TagType = 'shape' then t.TagName end, ', ') WITHIN GROUP (order by t.TagName) AS Shapes,
       STRING_AGG(case when t.TagType = 'size' then t.TagName end, ', ') WITHIN GROUP (order by t.TagName) AS Sizes
FROM dbo.account a
INNER JOIN dbo.tag t ON a.AccountID = t.AccountID
GROUP BY a.AccountID

Which yields the result...

CodePudding user response：

An alternative to AlwaysLearning's excellent answer is just simple sub-queries e.g.

SELECT a.AccountId 
    , (SELECT STRING_AGG(t.TagName, ', ') FROM dbo.Tag t WHERE t.AccountId = A.AccountId AND t.TagType = 'color' ) AS Colors
    , (SELECT STRING_AGG(t.TagName, ', ') FROM dbo.Tag t WHERE t.AccountId = A.AccountId AND t.TagType = 'shape' ) AS Shapes
    , (SELECT STRING_AGG(t.TagName, ', ') FROM dbo.Tag t WHERE t.AccountId = A.AccountId AND t.TagType = 'size' ) AS Sizes
FROM dbo.Account a;