7

I am trying to query two tables and get results like the following:

Section    Names
shoes      AccountName1, AccountName2, AccountName3
books      AccountName1

The tables are:

CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));

INSERT dbo.TableA(ID, Section, AccountId) VALUES
(1 ,'shoes','A1'),
(2 ,'shoes','A2'),
(3 ,'shoes','A3'),
(4 ,'books','A1');

CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));

INSERT dbo.TableB(AccountId, Name) VALUES
('A1','AccountName1'),
('A2','AccountName2'),
('A3','AccountNAme3');

I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing. I have tried the below query and get the error message:

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

I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.

Here is the query I am currently trying to get working correctly.

SELECT section, names= STUFF((
    SELECT ', ' + Name FROM TableB as b 
WHERE AccountId = b.AccountId
FOR XML PATH('')), 1, 1, '')
FROM TableA AS a
GROUP BY a.section
12

Sorry, I missed a step in the relationship. Try this version (though Martin's will work as well):

SELECT DISTINCT o.section, names= STUFF((
    SELECT ', ' + b.Name 
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o;

An approach that is at least as good, but sometimes better, is switching from DISTINCT to GROUP BY:

SELECT o.section, names= STUFF((
    SELECT ', ' + b.Name 
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o
GROUP BY o.section;

At a high level, the reason DISTINCT applies to the entire column list. Therefore for any duplicates it has to perform the aggregate work for every duplicate before applying DISTINCT. If you use GROUP BY then it can potentially remove duplicates before doing any of the aggregation work. This behavior can vary by plan depending on a variety of factors including indexes, plan strategy, etc. And a direct switch to GROUP BY may not be possible in all cases.

In any case, I ran both of these variations in SentryOne Plan Explorer. The plans are different in a few minor, uninteresting ways, but the I/O involved with the underlying worktable is telling. Here is DISTINCT:

enter image description here

And here is GROUP BY:

enter image description here

When I made the tables larger (14,000+ rows mapping to 24 potential values), this difference is more pronounced. DISTINCT:

enter image description here

GROUP BY:

enter image description here

In SQL Server 2017, you can use STRING_AGG:

SELECT a.section, STRING_AGG(b.Name, ', ')
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = a.Section
    GROUP BY a.section;

The I/O here is almost nothing:

enter image description here


But, if you're not on SQL Server 2017 (or Azure SQL Database), and can't use STRING_AGG, I have to give credit where credit is due... Paul White's answer below has very little I/O and kicks the pants off of both of the FOR XML PATH solutions above.

enter image description here


Other enhancements from these posts:

Also see:

  • can you tell me why Grouping By o.section is faster than DISTINCT o.section ? – Abou-Emish Aug 29 '18 at 14:28
  • 1
    @Abou-Emish Updated my answer. If you're on 2016 or lower, you'll want to use Paul's solution below instead. – Aaron Bertrand Aug 29 '18 at 14:30
10

I thought I would try a solution using XML.

SEDE Demo

Tables

DECLARE @TableA AS table
(
    ID integer PRIMARY KEY,
    Section varchar(10) NOT NULL,
    AccountID char(2) NOT NULL
);

DECLARE @TableB AS table
(
    AccountID char(2) PRIMARY KEY,
    Name varchar(20) NOT NULL
);

Data

INSERT @TableA
    (ID, Section, AccountID)
VALUES
    (1, 'shoes', 'A1'),
    (2, 'shoes', 'A2'),
    (3, 'shoes', 'A3'),
    (4, 'books', 'A1');

INSERT @TableB
    (AccountID, Name)
VALUES
    ('A1', 'AccountName1'),
    ('A2', 'AccountName2'),
    ('A3', 'AccountName3');

Join and convert to XML

DECLARE @x xml =
(
    SELECT
        TA.Section,
        CA.Name
    FROM @TableA AS TA
    JOIN @TableB AS TB
        ON TB.AccountID = TA.AccountID
    CROSS APPLY
    (
        VALUES(',' + TB.Name)
    ) AS CA (Name)
    ORDER BY TA.Section
    FOR XML AUTO, TYPE, ELEMENTS, ROOT ('Root')
);

XML creation query

The XML in the variable looks like this:

<Root>
  <TA>
    <Section>shoes</Section>
    <CA>
      <Name>,AccountName1</Name>
    </CA>
    <CA>
      <Name>,AccountName2</Name>
    </CA>
    <CA>
      <Name>,AccountName3</Name>
    </CA>
  </TA>
  <TA>
    <Section>books</Section>
    <CA>
      <Name>,AccountName1</Name>
    </CA>
  </TA>
</Root>

Query

The final query shreds the XML into sections, and concatenates the names in each:

SELECT
    Section = 
        N.n.value('(./Section/text())[1]', 'varchar(10)'),
    Names = 
        STUFF
        (
            -- Consecutive text nodes collapse
            N.n.query('./CA/Name/text()')
            .value('./text()[1]', 'varchar(8000)'), 
            1, 1, ''
        )
-- Shred per section
FROM @x.nodes('Root/TA') AS N (n);

Result

Output

Execution plan

Execution plan

-3

enter image description here

Demo table

enter image description here

department table

enter image description here

select 
    g.Department,
    t.numberofemp,
    t.totalslary,
    t.totalage,
    t.name 
from (
    select  
        count(*) numberofemp,
        tl.dtid,
        sum(salary) as totalslary,
        sum(age) as totalage,
        name=
            (stuff((
                select  DISTINCT  
                    ', '+ [name] 
                from  demo as d 
                inner join demodept as dt 
                    on d.dtid=dt.dtid 
                where dt.dtid=tl.dtid 
            for xml path(''),type).value('(./text())[1]','varchar(max)'),1,2,''))  
 -- from demo as d inner join demodept as dt on d.dtid=dt.dtid
    from Demo as tl
    group by tl.dtid) as t 
inner join  demodept as g 
    on  t.dtid=g.dtid

Output

  • Welcome to DBA.SE! You forgot your output, it seems ... – Glorfindel Nov 30 '18 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.