Output of Pivot table in temp table

Question

Post reply

Output of Pivot table in temp table

jsshivalik

SSChasing Mays

Points: 612
More actions
April 28, 2022 at 9:42 am

#4023557
Hi
Is it possible to create Temp table according to Pivot output dynamically & show Grand Total
Below is the output from Pivot. Item can be upto n levels like Item1 , Item2 , Item3, Item4
```
NameItem1Item2
Loc-111
Loc-22Null
```
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

Have you tried using SELECT INTO?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jsshivalik SSChasing Mays Points: 612 More actions · Answer 2

Hi

Below code is giving error Incorrect syntax near )

declare @cols as nvarchar(max) = '';

declare @query as nvarchar(max) = '';

select @cols = @cols + QuoteName(itemname) + ',' from (select distinct itemname from #temp)

as tmp

select @cols = substring(@cols,0,len(@cols))

set @query =

'select * into #FinalResult from

(select Name, ' + @cols + ' from (select name,quantity,itemname from #temp) x

pivot (sum(quantity) for itemname in (' + @cols + ')) piv ';

)

execute (@query)

Thanks

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Try this

SET @query
    = N'select * into #FinalResult from

(select Name, ' + @cols
      + N' from (select name,quantity,itemname from #temp) x

pivot (sum(quantity) for itemname in (' + @cols + N')) piv 

) x';

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 4

Phil Parkin wrote:

Try this

SET @query
    = N'select * into #FinalResult from

(select Name, ' + @cols
      + N' from (select name,quantity,itemname from #temp) x

pivot (sum(quantity) for itemname in (' + @cols + N')) piv 

) x';

Note that the temporary table will only exist within the scope of the dynamic statement here though, so after EXEC sys.sp_executesql @query has been run, the table would be dropped; so you wouldn't be able to refer to it outside of the dynamic pivot.

I suspect there's more to the story here; we already found out that the OP is asking about a dynamic pivot, rather than a standard one. What is the goal of putting the data into a (temporary) table here? Normally pivoting such as this is better off in your presentation layer; if you want to work with the data in your RDBMS leave it normalised.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jsshivalik SSChasing Mays Points: 612 More actions · Answer 5

Hi

It does not show results. It gives message 2 rows affected. I have written execute(@query) also

Thanks

Thom A SSC Guru Points: 99133 More actions · Answer 6

jsshivalik wrote:

Hi
It does not show results. It gives message 2 rows affected. I have written execute(@query) also
Thanks

Of course it won't, the data was inserted into a table, not returned to the application. If you want to INSERT it into a temporary table, why do you want to return the resultset to the application too? We definitely don't have the full picture; explain your full requirements.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jsshivalik SSChasing Mays Points: 612 More actions · Answer 7

Hi

Main requirement is to show data in Pivot & display Grand Totals of columns. Columns will be dynamic.

Display the results

Thanks

This reply was modified 2 years, 7 months ago by jsshivalik.

Thom A SSC Guru Points: 99133 More actions · Answer 8

jsshivalik wrote:

Hi
Main requirement is to show data in Pivot & display Grand Totals of columns. Columns will be dynamic.
Display the results
Thanks

So as mentioned, why not pivot your data in the presentation layer; that is by far the best place. Otherwise, if you "must" do this in the SQL layer, I suggest switching to using conditional aggregation, then you don't have to clumb your data into a temporary table to then try and work out the grant totals too.

If you don't know how to do this, post your sample data as DDL and DML statements and show the results you want.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jsshivalik SSChasing Mays Points: 612 More actions · Answer 9

Hi Thom

First i get data saved in Temp table like below

CodeNameQuantityDscription
32Rohan1        Item 1
32Rohan2        Item 2
36Sunny1        Item 2

Actual output i want like below

NameItem 1Item 2
Rohan12
Sunny 1
Grand Total13

Phil Parkin SSC Guru Points: 246979 More actions · Answer 10

This should be done in the presentation layer (Excel, SSRS, PowerBI etc).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

jsshivalik wrote:

Hi Thom

First i get data saved in Temp table like below

CodeNameQuantityDscription
32Rohan1        Item 1
32Rohan2        Item 2
36Sunny1        Item 2

Actual output i want like below

NameItem 1Item 2
Rohan12
Sunny 1
Grand Total13

This is a "dynamic" Pivot. I don't use Pivots at all. They're generally slower and more difficult to manipulate than an ancient "Black Art" known as a CROSS TAB.

Whether the needed query is dynamic or not and if need to know more about CROOSSTabs, see the following article:

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

If the needed query does need to be dynamic, then also see the this article, as well...

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)