SQL DATABASE TO PIVOT TABLE

Question

SQL DATABASE TO PIVOT TABLE

pjesrani

Say Hey Kid

Points: 695
More actions
November 15, 2007 at 11:52 am

#153965

Hi,
I need to work with data which is huge and does not fit into the excel sheet. I want to transfer the data directly from the database to a pivot table using siss. How do i go about doing it.
Please help:)

Viewing 15 posts - 1 through 15 (of 17 total)

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

Adron SSC Veteran Points: 228 More actions · Answer 1

Could you clarify a bit more? When you say it won't fit into Excel, do you mean that the data is not entirely visible in the window or that the data exceeds the rows.

pjesrani Say Hey Kid Points: 695 More actions · Answer 2

pjesrani

Say Hey Kid

Points: 695

November 18, 2007 at 1:18 am

#752096

yes it exceeds the no of cells

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

yes it exceeds the no of cells

Heh... not quite the answer we were looking for... but it'll do.

Two ways to do this... create a view with the pivot and use the "External Data" feature of Excel, or create a proc that outputs the spreadsheet using OPENROWSET.

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

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 4

In Excel, create a pivot table (Data/Pivot Table and Pivot Chart Report...) and choose an external data source ([External Data Source] -- your table/view). The data can exceed the maximum number of rows that would be allowed in a worksheet. (I expect there is a limit, but it is much larger than the 65k or so worksheet row limit, and I haven't hit it yet.)

pjesrani Say Hey Kid Points: 695 More actions · Answer 5

Hi Jef/Jim,

Thanks for your early help, however i think that i am not very clear while asking the question. All i need to do is extract the data directly from the SQL database to a pivot table in excel. I have tried both your methods but none seem to be working.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

What type of error did you get trying to load the pivot table directly?

How many rows are you working with?

pjesrani Say Hey Kid Points: 695 More actions · Answer 7

This is the error that i am getting

TITLE: Package Validation Error

------------------------------

Package Validation Error

------------------------------

ADDITIONAL INFORMATION:

Error at Data Flow Task [Pivot [16]]: No Pivot Key found. One input column must be used as the Pivot Key.

Error at Data Flow Task [DTS.Pipeline]: "component "Pivot" (16)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------

BUTTONS:

OK

------------------------------

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 8

But that seems to be the DTS/SSIS error. Have you just tried to get the pivot with a direct load from Excel?

pjesrani Say Hey Kid Points: 695 More actions · Answer 9

I have SELECTED OLE DB SOURCE OPTIONS FROM DATAFLOW SOURCES,

pivot option from data flow transformations and excel destination option for data flow destination.

I have linked these 3 and when i try to execute the package it gives me the above error.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 10

You should not need the package. Just open Excel and create a pivot table using an external data source (your view in your server).

Or am I missing something?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

pjesrani (11/18/2007)
yes it exceeds the no of cells

By the way - you didn't mention in which direction it "exceeds". If vertically (i.e. rows>65K) then yes - the options discussed above should help; on the other hand - if you have more than 256 columns, Excell will NOT display that. That's one of the "not negotiable" things about excel as i recall.

How big is this then when you produce the output in Query analyzer? rows x columns?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

pjesrani Say Hey Kid Points: 695 More actions · Answer 12

Hey guys you know what its working Thanks a lot, however i was wondering if i could schedule it.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 13

If you chose to go direct to the Excel Pivot table, use Pivot Table Options, and select "Refresh on Open." Not quite the same as an automatic update, but who can ever tell?

pjesrani Say Hey Kid Points: 695 More actions · Answer 14

yup, however can i do it throut ssis and shedule it ???