SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loading T-SQL Statements from a database


Loading T-SQL Statements from a database

Author
Message
Kevin.roberts25
Kevin.roberts25
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20679 Visits: 7660
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
Kevin.roberts25
Kevin.roberts25
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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)
Sioban Krzywicki
Sioban Krzywicki
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4480 Visits: 8096
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
Kevin.roberts25
Kevin.roberts25
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 12
SSRS and asp.net
Sioban Krzywicki
Sioban Krzywicki
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4480 Visits: 8096
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
David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16671 Visits: 10111
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.


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215710 Visits: 41980
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search