Report Generation Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Report Generation Help

Author

Topic

jp2sql
Starting Member

3 Posts

Posted - 2008-02-19 : 09:36:39

Hello! This is my first time on this particular forum, and I come here because I have an SQL question that no one seems to be able to answer.

First, let me say that I am not that skilled in SQL. I'm a software guy that knows a little about SQL, not the other way around. My C# application makes SQL queries, and I use the results to display data in our applications.

With that said, I am looking for a way to produce a report from a single table.

Instead of this being a standard report that returns columns A, B, and C, I need to return columns A, B, and C for Shift 1, columns A, B, and C for Shift 2, and columns A, B, and C for Shift 3. The catch is, I need to display the report as:


  |_Col_|__Shift_1__|__Shift_2__|__Shift_3__|
  |  A  |  B1 |  C1 |  B2 |  C2 |  B3 |  C3 |
  |  A  |  B1 |  C1 |  B2 |  C2 |  B3 |  C3 |
  |  A  |  B1 |  C1 |  B2 |  C2 |  B3 |  C3 |
                  Report 1

Is there a way to do this? All of the data is contained in one table.

In other words, is there a way to construct an SQL statement that groups multiple results into columns, or do I need to call multiple SQL statements?

If I have to use multiple SQL statements, how would I take that output and produce the type of report I need shown in Report 1 above?

Currently, I am displaying everything using an HTML Table, but this is an internal company application, and the group really does not like the report generating in a web browser.

Thanks in advance for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 09:44:00

[code]SELECT t1.A,
t2.B + ' ' +t2.C as Shift1,
t3.B + ' ' +t3.C as Shift2,
t4.B + ' ' +t4.C as Shift3
FROM Table t1
INNER JOIN (SELECT A,B,C
FROM Table
WHERE Shift='Shift1')t2
ON t2.A=t1.A
INNER JOIN (SELECT A,B,C
FROM Table
WHERE Shift='Shift2')t3
ON t3.A=t1.A
INNER JOIN (SELECT A,B,C
FROM Table
WHERE Shift='Shift3')t4
ON t4.A=t1.A[/code]

jp2sql
Starting Member

3 Posts

Posted - 2008-02-19 : 10:11:44

Hi visakh16, and thanks for a fast response!

I entered the query as you pointed out, and I got this:

quote:
A message from SQL Server Enterprise Manager
[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

Does this mean that this query is using a lot of resources? I certainly don't want the company VPs seeing this message whenever they attempt to run my application.

If I back up the transaction log as the message suggests, how could I prevent this error from returning to my application?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 10:25:10

This is not directly related to execution of this statement. This is just saying that the space alloted to your db for logging has been exhausted. You need to free some space in your tempdb. Truncate part of your log files if you have permissions. Else ask your db guys to do it for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources