I am doing a pivot where the sql is dynamic as I don't know the column names
until done.

But I am trying to get totals for my rows and totals for my columns (the
pivoted columns).

How would I do that if I have the following:

I am using:

SET @sql = '
select * from #tfs
PIVOT
(
SUM(Number)
for retailer in('+@strList+')
) as pvt
'

To get.

select * from #tfs
PIVOT
(
SUM(Number)
for retailer in([Sears], [CompUSA], [Lowes], [None], [K-Mart])
) as pvt

Thanks,

Tom

I finally got this to work using the following:

SET @sql = '
select * from #tfs
PIVOT
(
SUM(Number)
for retailer in('+@strList+')
) as pvt
UNION
select 'Grand Total', from #tfs
PIVOT
(
SUM(Number)
for retailer in('+@strList+')
) as pvt
'
The problem is that the 2nd Select after the UNION is being put at the 3rd
row instead of at the bottom???

How do you get it to put the results of the 2nd select after the 1st
select???

Thanks,

Tom

"tshad" <tfs (AT) dslextreme (DOT) com> wrote

On Tue, 26 Oct 2010 15:32:16 -0700, tshad wrote:

Hi Tom,

You need to add an ORDER BY at the end of the query:

ORDER BY CASE WHEN Retailer = 'Grand Total' THEN 2 ELSE 1 END

I also think there is something missing after the comma in the second
union'ed query, but since you already got it working, I suppose that's a
copy/paste error.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Wed, 27 Oct 2010 11:54:30 +0200, Hugo Kornelis wrote:

Correction. I just realised the first column will not be called
Retailer, as retailer is the pivot column. And since there are no
non-pivoted columns, your query will only produce grand totals to begin
with. If you want a row for (eg) each Product, plus a grand total, then
you'd need something like

select * from #tfs
PIVOT
(
ProductName,
SUM(Number)
for retailer in('+@strList+')
) as pvt
UNION
select 'Grand Total', from (select Number, retailer from #tfs
PIVOT
(
SUM(Number)
for retailer in('+@strList+')
) as pvt
ORDER BY CASE WHEN ProductName = 'Grand Total' THEN 2 ELSE 1 END;

Oh, and you can probably use UNION ALL instead of UNION, that would
perform a bit better.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis