CrossTab in SQL

Question

Post reply

CrossTab in SQL

barry.nielson

SSC Eights!

Points: 912
More actions
September 12, 2018 at 5:10 pm

#368166

Hi all,
I have two queries in access
* Collect data from which two text boxes supply the date range conditions: To and From.
* Cross cross tab query based on the first query. This query is used to supply the information for an excel report.
Connected to SQl linked tables, these queries are slow and cumbersome. I was considering putting them into SQL as views.
However, my understanding is that SQL views don't accept parameters and stored procedures don't return select statements.
So can anybody provide me with some examples of how this could work. If you need more information, please feel free to ask.
Cheers

Viewing 6 posts - 1 through 5 (of 5 total)

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Stored procedures do return select statements so you options are a stored procedure or a table valued function.

pietlinden SSC Guru Points: 63428 More actions · Answer 2

Views don't accept parameters, but stored procedures do.
Jeff Moden has some articles on doing crosstabs in T-SQL... You can use PIVOT too.
Here's an article...

CREATE PROC myProc @FromDate DATE, @ToDate DATE AS SELECT... FROM ... WHERE SomeDate>=@FromDate AND SomeDate<=@ToDate;

andycadley SSCertifiable Points: 5296 More actions · Answer 3

Given the choice, I'd opt for a Table Valued Function over a Stored Procedure if possible. Yes SPs do return the results of Select statements and if all that matters is getting the results into Excel it probably won't matter. However if you decide later you need to combine it with some other data source, it's a lot easier to do with a TVF than with an SP as there isn't really a "clean" way of getting the result set from an SP from within SQL itself.

drew.allen SSC Guru Points: 76944 More actions · Answer 4

Joe Torre - Wednesday, September 12, 2018 6:09 PM
Stored procedures do return select statements so you options are a stored procedure or a table valued function.

To be technical, they don't return select statements, they return result sets.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

andycadley - Thursday, September 13, 2018 12:40 AM
Given the choice, I'd opt for a Table Valued Function over a Stored Procedure if possible. Yes SPs do return the results of Select statements and if all that matters is getting the results into Excel it probably won't matter. However if you decide later you need to combine it with some other data source, it's a lot easier to do with a TVF than with an SP as there isn't really a "clean" way of getting the result set from an SP from within SQL itself.

I agree... if you can pull it off, iTVFs can be quite superior to stored procedures and views.

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