Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Loading T-SQL Statements from a database Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 4:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 1:42 AM
Points: 6, Visits: 12
I want to call all of my heading from table A and call my select statements from Table B.
TABLE A
ID int ,
SequenceNo int,
ColumnHeading varchar(255)

TABLE B

ID int,
SequenceNo int,
SQLStatement nvarchar(500)

This is what I want to happen
the sql statement in table b =

  select count(somecolumn) from someTable [/code
[code="sql"]

Select a.ID, a.ColumnHeading, sum( b.SQLStatement )
inner join a.SequenceNo on b.SequenceNo

is something like this even possibe
Post #1421888
Posted Wednesday, February 20, 2013 1:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:18 PM
Points: 6,250, Visits: 7,409
I think part of it may be lingo usage, but I'm unfortunately not really sure what you're trying to do here.

Can you explain what you'd like to do in plain english as a business requirement? Maybe we can turn it into tech-speak afterwards. I for one am lost as to your intent.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1421965
Posted Wednesday, February 20, 2013 2:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 1:42 AM
Points: 6, Visits: 12
Ok I have 26 reports to create, the end result would look something like this

Description, Period1, Period2, Period3


Average Sales, 25, 45, 67
Totals Team 1, 53, 74, 27
Totals Team 2, 36,49, 35
Totals Team 3, 36,49, 35
Totals Team 4, 36,49, 35

for the descriptions of all the report rows I have stored them in a table TABLE A
ID INT,
ReportNo int,
LineSequence int, -- This is the order I want the description to be on the report
Description varchar(255) -- examples Totals Team 1

to calculate this what I want is the following. I will be loading the descriptions by passing the report number and ordering by Description. I want to call the rellevant sql queries stored in a table that will allow me to

Description, Period1, Period2, Period3


Average Sales, (sql query from database where @ReportNo =1 and @LineSequence=1 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 1,(sql query from database where @ReportNo =1 and @LineSequence=2 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 2, (sql query from database where @ReportNo =1 and @LineSequence=3 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 3,(sql query from database where @ReportNo =1 and @LineSequence=4 and @Period=1 ),(sql query from database), (sql query from database)
Totals Team 4,(sql query from database where @ReportNo =1 and @LineSequence=5 and @Period=1 ),(sql query from database), (sql query from database)




Post #1421987
Posted Wednesday, February 20, 2013 10:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:07 PM
Points: 2,766, Visits: 7,241
What are you using to create the report? That's the key here.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #1422217
Posted Wednesday, February 20, 2013 5:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 1:42 AM
Points: 6, Visits: 12
SSRS and asp.net
Post #1422363
Posted Wednesday, February 20, 2013 6:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:07 PM
Points: 2,766, Visits: 7,241
Do you have to do header and body in the same query? I haven't worked much with SSRS. If you create two queries and feed the same parameters to them, that should work. Create one query for the header and one for the body.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #1422374
Posted Thursday, February 21, 2013 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 7,100, Visits: 6,923
You will need to build a defined dataset using dynamic sql to union your statements or build a temp table and insert data interatively.
If your dataset contains the columns ID,SequenceNo,ColumnHeading and ColumnValue then you can build a matrix in SSRS with ID for the row, ColumnValue for the column (grouped on SequenceNo) and ColumnHeading for the column heading.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1422428
Posted Tuesday, February 26, 2013 9:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
Kevin.roberts25 (2/20/2013)
Ok I have 26 reports to create, the end result would look something like this

Description, Period1, Period2, Period3


Average Sales, 25, 45, 67
Totals Team 1, 53, 74, 27
Totals Team 2, 36,49, 35
Totals Team 3, 36,49, 35
Totals Team 4, 36,49, 35

for the descriptions of all the report rows I have stored them in a table TABLE A
ID INT,
ReportNo int,
LineSequence int, -- This is the order I want the description to be on the report
Description varchar(255) -- examples Totals Team 1

to calculate this what I want is the following. I will be loading the descriptions by passing the report number and ordering by Description. I want to call the rellevant sql queries stored in a table that will allow me to

Description, Period1, Period2, Period3


Average Sales, (sql query from database where @ReportNo =1 and @LineSequence=1 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 1,(sql query from database where @ReportNo =1 and @LineSequence=2 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 2, (sql query from database where @ReportNo =1 and @LineSequence=3 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 3,(sql query from database where @ReportNo =1 and @LineSequence=4 and @Period=1 ),(sql query from database), (sql query from database)
Totals Team 4,(sql query from database where @ReportNo =1 and @LineSequence=5 and @Period=1 ),(sql query from database), (sql query from database)



This is known as a "Crosstab" or "Pivot". Please see the following articles for high performance solutions on the subject.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/

If you'd like a specific coded answer, please see the article at the first link below.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424327
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse