In addition, PIVOT can only do one aggregate, but you need two different aggregates in this example:

1. Number of complaints
2. Average closing time

Crosstabs are more flexible in addition to what...

The ABS(CAST(BE.Buck1 AS tinyint) - 1) doesn't work for me. It is zeroing out the flag that tells me whether or not to include the bucket.

Here's what I've come...

The whole purpose of having a CROSS APPLY/OUTER APPLY with a TOP(1) is so that you can avoid multiple reads of the table (although the sort may offset that advantage...

• This reply was modified 5 years, 8 months ago by drew.allen. Reason: Corrected spelling mistake and changed the OUTER APPLY to a CROSS APPLY. The ISNULL() was no longer necessary due to the change to CROSS APPLY

Without sample data/expected results, it's pretty difficult to come up with a solution.  I suspect that you'll either want to use a CROSS APPLY/OUTER APPLY with a TOP(1) or a...

I think this is simpler.  I used a CROSS JOIN, because it wasn't clear how to link the two tables.

SELECT *
FROM #OrigTable AS ot
CROSS JOIN #BucketExclude AS...

I believe that this solves your problem.  It's a variation on interval packing as discussed by Itzik Ben-Gan.

create table #t ( b decimal(13,4) not null, c decimal(13,4)...

Since you've posted this in the SQL-2019 forum, you can use STRING_AGG() (introduced in SQL-2017).

Drew

Please provide sample data in the form of a script to either create a temporary table or declare a table variable and an INSERT statement to insert values into said...

For me, 2019 was mostly good, but the last month was absolutely devastating.  My best friend of 40 years and the only person from college that I kept in touch...

The problem is the order of precedence of operators.  The FOR XML is evaluated before the UNION, but your query requires that it be evaluated after.  Use a CTE for...

You'll learn a lot more if you do your own homework.

Drew

You oversimplified to the point where you did not provide enough information to answer the question, so, no, we don't get the idea.  I suspect that you should be using...

Use a CROSS APPLY with a TOP(1).

If you provide consumable data, you'll get tested code.

Drew

PS: I highly recommend NEVER using NULLs when working with intervals.  NULL values make the calculations...

You should ALWAYS provide the whole query, because the location where the error is detected is often not the location that needs to be fixed.

The logical order of processing is...

An alternative solution

;WITH cte AS
(
    SELECT s.Teams, s.Category, SUM(s.Sales) Sales
      FROM dbo.Sales s
     GROUP...