Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Loading T-SQL Statements from a database
Add to briefcase

Loading T-SQL Statements from a database Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Kevin.roberts25
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
Evil Kraig F
Posted Wednesday, February 20, 2013 1:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 5,658, Visits: 6,099
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
Kevin.roberts25
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
Stefan Krzywicki
Posted Wednesday, February 20, 2013 10:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 2,418, Visits: 6,009
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
Kevin.roberts25
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
Stefan Krzywicki
Posted Wednesday, February 20, 2013 6:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 2,418, Visits: 6,009
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
David Burrows
Posted Thursday, February 21, 2013 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 6,350, Visits: 5,355
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
Jeff Moden
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 @ 6:33 PM
Points: 32,889, Visits: 26,757
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1424327
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.