Output of Pivot table in temp table

  • 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

  • Have you tried using SELECT INTO?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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';

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • Hi

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

    Thanks

  • 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

  • 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 1 month, 4 weeks ago by  jsshivalik.
  • 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

  • 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 should be done in the presentation layer (Excel, SSRS, PowerBI etc).

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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)
    Intro to Tally Tables and Functions

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

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