Custom Reports with SQL - 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

Transact-SQL (2000)

Custom Reports with SQL

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-03-25 : 14:09:18

Our HR Department requires a report on Employees based on what shift they work on and what department they are in.

In our database, the EmployeeInfo table is set up simply as:


Declare Name varchar(255)
Declare Department varchar(255)
Declare Shift varchar(50)

How do I pull the data so that it produces this type of output:


<table>
  <tr>
    <th colspan="3">Maintenance Department</th>
  </tr>
  <tr>
    <th>First Shift</th>
    <th>Second Shift</th>
    <th>Third Shift</th>
  </tr>
  <tr>
    <td>John1 DoeA</td>
    <td>John1 DoeB</td>
    <td>John1 DoeC</td>
  </tr>
  <tr>
    <td>John2 DoeA</td>
    <td>John2 DoeB</td>
    <td>John2 DoeC</td>
  </tr>
  <tr>
    <td>John3 DoeA</td>
    <td>John3 DoeB</td>
    <td></td>
  </tr>
  <tr>
    <td>John4 DoeA</td>
    <td>John4 DoeB</td>
    <td></td>
  </tr>
  <tr>
    <td>John5 DoeA</td>
    <td></td>
    <td></td>
  </tr>
</table>

(Note: I'm not trying to generate HTML output; but I don't know how to create a table using the Snitz Forum's syntax. I hope the HTML syntax gets the point across.)

Notice that 2nd and 3rd shifts may have blank entries! Also, after the example department "Maintenance" above is displayed, further departments should be displayed next (HR Department, IT Department, Welding Department, etc.).

How do I write a query for this so that I can then fill my DataGridView control?

If I use another control (i.e. ReportViewer or Crystal Reports), it would also requires the data to be in some kind of tabular format.

Thanks in advance for any help with this.
~Joe

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-03-30 : 16:04:26

Is this task not possible?

Currently, the only way I know of to do this is to read it all into a temporary data table and sort items line-by-line into an array which I re-visit afterwards to construct the data table that I fill my Data Grid View object with.

Is there a better alternative?

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-04-09 : 18:13:15

Bump.

Avoid Sears Home Improvement

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-04-09 : 23:16:10

Dear Sir,

Here are the sample of idea on how to doing that:-


CREATE TABLE EMPLOYEE
(
 eName varchar(255),
 Department varchar(255),
 Shift varchar(50)
)

INSERT INTO EMPLOYEE(eName,Department,Shift)
values
('John1 DoeA','IT','Shift1')
INSERT INTO EMPLOYEE(eName,Department,Shift)
values
('John1 DoeB','IT','Shift2')
INSERT INTO EMPLOYEE(eName,Department,Shift)
values
('John1 DoeC','IT','Shift3')

select department,Shift1=max(Shift1),Shift2=max(Shift2),Shift3=max(Shift3) from
(
select department,
Shift1= case when shift='Shift1' then eName  else '' end ,
Shift2= case when shift='Shift2' then eName  else '' end ,
Shift3= case when shift='Shift3' then eName  else '' end 
from employee 
) C group by department

Thank you.

Regards,
Michelle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources