how to get grand total row in pivot concept??? urgent plz...... Thank u all

Question

Post reply

how to get grand total row in pivot concept??? urgent plz...... Thank u all

kavinithi

Old Hand

Points: 397
More actions
August 25, 2011 at 11:42 pm

#256319

i want to have a summerized row at the end of my result.
I used pivot concept in my procedure. How can i add a row at the end of the pivot tsble as 'Total'
Thank u all
Regards,
kavitha.

Viewing 10 posts - 1 through 9 (of 9 total)

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

I see this is your first ever post here, Kavitha, so Welcome to SSC!!

Now, we could not gather much information from your question. Can you please brief us about what you want to do?

You may also want to post some sample data, your table structure ( all mock-ups, no real data) and an expected output.

Some like:

DECLARE @FirstTable TABLE

(

Name VARCHAR(10)

,id INT

,value VARCHAR(10)

)

INSERT INTO @FirstTable

SELECT 'A1',1,100

UNION ALL SELECT 'A2',1,200

UNION ALL SELECT 'A3',1,300

UNION ALL SELECT 'B1',2,100

UNION ALL SELECT 'B2',2,200

DECLARE @SecondTable TABLE

(

id INT

,value VARCHAR(10)

)

INSERT INTO @SecondTable

SELECT 1,50

UNION ALL SELECT 1,25

UNION ALL SELECT 1,100

UNION ALL SELECT 2,50

UNION ALL SELECT 2,150

UNION ALL SELECT 2,50

Expected Result:

A1150

A11100

A2125

A21100

A3125

B1250

B12150

B2250

This will get us started right away in coding for your requirement. Also it will yield into getting a tested code 🙂

Hope to see you with more information, and once again, warm welcome!

LutzM SSC Guru Points: 107049 More actions · Answer 2

@ColdCoffe: are you sure the sample code in this post is related to the original question?

Seems like it belongs to "the other thread"... 😉

@kavinithi: you would need to wrap the pivot statement in a subquery and use the ROLLUP operator at the end.

The pivot concept itself usually is unrelated to a grouping operation. If it'S not in your scenario, please provide some ready to use sample data as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

kavinithi Old Hand Points: 397 More actions · Answer 3

🙂 Ofcourse i'm new to this.

Thank u very much all of u who replied me.

Finally i found the solution.

I had selected the output of the PIVOT table into a temp table and then used union all to include a new row called TOTAL with the appropriate cols.

Thank u all once again.

Regards,

Kavitha.

bisht.kuldeep SSC Enthusiast Points: 194 More actions · Answer 4

You Can Use Following , Use Following Sample Code

--Create Table PivotTable

Drop Table PivotTable

Create Table PivotTable(ProductName Varchar(100),MarketName Varchar(100),Price int)

Go

--insert Values Into Table

Insert Into PivotTable

Values

('Orange','Reliance Fresh',100),

('Mango','Big Bazar',120),

('Orange','Big Apple',30),

('Banana','CO-MART',150),

('Mango','CO-MART',75),

('Grape','Mandi',75)

Go

--Declaration Of Variable For Dynamic columns.

Declare @PivotCols Varchar(2000)

Set @PivotCols=''

Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))

Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)

Go

Declare @PivotCols Varchar(2000)

Declare @PivotCols1 Varchar(2000)

Set @PivotCols=''

Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))

Select @PivotCols1=(Select Distinct 'Sum(['+ MarketName +']) As ['+MarketName+'],' from PivotTable for Xml Path(''))

Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)

Set @PivotCols1=SUBSTRING(@PivotCols1,1,len(@PivotCols1)-1)

--set @PivotCols1=ReplacE(ReplacE(@PivotCols,'[','Sum(['),']','])')

--Dynemic Query To Run Pivot

exec('SElect Case When PRoductName Is null Then ''Total'' else PRoductName End as ProductName,'+@PivotCols1+' From (Select * From PivotTable as tmp pivot (sum(price) for MarketName in ('+@PivotCols+')) as tbl)Tab

Group by PRoductName

with Rollup ')

Go

For Details You Can Check

http://mssqlguide.kuldeepbisht.com/2011/06/create-dynamic-pivot-table-in-ms-sql.html

Thanks

Kuldeep Bisht

Simplion Technologies

http://mssqlguide.kuldeepbisht.com

Kuldeep Bisht
Simplion Technologies
http://mssqlguide.kuldeepbisht.com

nithiyanandam-447806 Ten Centuries Points: 1197 More actions · Answer 5

Hi Kavitha,

Refer this...

http://www.codeproject.com/KB/database/SQLDynamicPivots.aspx

This would help you something...

Thanks,

Nithi

LutzM SSC Guru Points: 107049 More actions · Answer 6

Like I said before, the PIVOT is unrelated to the total.

But in case some sort of a dynamic pivot is required, I would prefer the DynamicCrossTab as described in the related link in my signature. My preference is not only because of readybility but also because of the better performance in most cases.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

kavinithi (8/25/2011)
i want to have a summerized row at the end of my result.
I used pivot concept in my procedure. How can i add a row at the end of the pivot tsble as 'Total'
Thank u all
Regards,
kavitha.

Hi Kavitha and welcome to SSC!

Since you're new here, please take a look at the article at the first link in my signature line below... if you provide "readily consumable" data in such a fashion as described in that article along with an expected output example, people will be able to give you coded examples for your actual problem and they're likely to do it rather quickly.

You might also want to avoid the term "urgent"... it won't win you any friends here. EVERY problem is "urgent". 🙂

--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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 8

LutzM (8/26/2011)
@ColdCoffe: are you sure the sample code in this post is related to the original question?
Seems like it belongs to "the other thread"... ;-).

Lutz, you are right, this sample data belongs "the other thread" 😉 . I just wanted the OP to get a gist of how to present her data so that people can jump on to working on the request . My apologies if it had caused confusion 🙂

kavinithi Old Hand Points: 397 More actions · Answer 9

"You might also want to avoid the term "urgent"... it won't win you any friends here. EVERY problem is "urgent". "

Hi Jeff,

Many Thanks for your valuable comments:-).

Sure i'l do accordingly hereafter.

Anyway i found out the solution after posting this query....

Since i'm new to my current job, i couldn't address my problem correctly.

Regards,

kavitha.