Pivot rows won't group

Transact-SQL
1

I have an initial grouping query which groups by columns InTerm and ExTerm .
The result of that query is placed into a temp table. I have unioned the result up for you to help me reproduce the failure in the misbehaving Pivot query below it.

IF OBJECT_ID('tempdb..#tempAudit') IS NOT NULL 
  DROP TABLE dbo.#tempAudit;

select * into #tempAudit from (
select 2 as InTerm, 2 as ExTerm, 
       5 as Out_Quantity, 5 as QuantityExported 
union 
select 2,	3,	970,	970 
union
select 3,	3,	192,	192 
union
select 4,	4,	1113,	1113) x;

Then I use a misbehaving Pivot query to put the InTerms into columns, and the ExTerms into rows, as shown:

SELECT ExTerm as "Export Term",OUT_Quantity as "Exported", 
[2], [3], [4]
from (SELECT * from #tempAudit) x
pivot (sum(QuantityExported) for InTerm 
in ( [2], [3], [4])) p 
order by ExTerm;

/*
Export Term		Exported	2			3		4
	2			5			5			NULL	NULL
	3			192			NULL		192		NULL
	3			970			970			NULL	NULL
	4			1113		NULL		NULL	1113  */

Do you see how the row for Export Term 3 is duplicated?
I want a crosstab that looks like this instead:

/*
    Export Term		Exported	2			3		4
    	2			5			5			NULL	NULL
    	3			1162		970			192		NULL
    	4			1113		NULL		NULL	1113     */

Must I rework this to an SSRS report with group totals, or is there a way to do it within the crosstab?

Thank you!

2 
SELECT 
    ExTerm as "Export Term",
    SUM(OUT_Quantity) as "Exported", 
    SUM(CASE WHEN InTerm = 2 THEN Out_Quantity END) AS [InTerm2],
    SUM(CASE WHEN InTerm = 3 THEN Out_Quantity END) AS [InTerm3],
    SUM(CASE WHEN InTerm = 4 THEN Out_Quantity END) AS [InTerm4]
FROM (SELECT * from #tempAudit) x
GROUP BY ExTerm
ORDER BY ExTerm;
3

Thank you Scott, but I do not know how many InTerms or ExTerms I will have before I start.

Is there any way to use the PIVOT command?

If not, must I construct a SQL statement on the fly?

4

Is this for a report in SSRS - or something else? It is possible to build a dynamic pivot/cross-tab but if the result is going to be used in a downstream application (such as SSRS or SSIS or even Crystal) then you cannot bind to a dynamic query.

See this article: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

5

If you don't know how many InTerms there are, how can you use a PIVOT command?

I'm not a big fan of PIVOT, and prefer cross tabs, which is why I used them here.

6

It is for something else. I am using it to build an Excel output.

I was able to use @ScottPletcher's answer to build dynamic SQL, so I will call this my answer.

7

You are right, Scott. I was using dynamic SQL to generate my PIVOT command, I changed it up to generate the Group-by SUM(CASE ...) construct instead.

Let's just say for the record that this is the only way to get around Pivot rows which won't group together, due to the way PIVOT works.

DECLARE @cols AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT ', SUM(CASE WHEN InTerm = ' 
				  + cast(InTerm as varchar) + ' THEN Out_Quantity END) AS ' 
				  + QUOTENAME( InTerm )
            FROM #tempAudit c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- print 'Columns = "'+ @cols + '"';
set @query = 'SELECT ExTerm as [Export Term], SUM(OUT_Quantity) as Exported, ' + @cols 
+ ' FROM (SELECT * from #tempAudit) x GROUP BY ExTerm ORDER BY ExTerm';

print 'Query = "'+ @query + '"';
execute(@query);


 (4 rows affected)
Query = "SELECT ExTerm as [Export Term], SUM(OUT_Quantity) as Exported,  SUM(CASE WHEN InTerm = 2 THEN Out_Quantity END) AS [2], SUM(CASE WHEN InTerm = 3 THEN Out_Quantity END) AS [3], SUM(CASE WHEN InTerm = 4 THEN Out_Quantity END) AS [4] FROM (SELECT * from #tempAudit) x GROUP BY ExTerm ORDER BY ExTerm"
 Warning: Null value is eliminated by an aggregate or other SET operation.