0

As an example, lets say my dataset holds:

EMPLOYEE_ID
EMPLOYEE_NAME
EMPLOYEE_ACCT_ID
EMPLOYEE_ACCT_TYPE
EMPLOYEE_ACCT_BALANCE

I would like to present the data in the following way:

EMPLOYEE   |   CHECKING   |   SAVINGS    |   INVESSTMENT   |   XMAS   |
_______________________________________________________________________

Mary       |     100.00   |     700.00   |      3,000.00   |    175.00
Jim        |     850.00   |     600.00   |      1,500.00   |      0.00

TOTAL      |     950.00   |    1,300.00  |      4,500.00   |    175.00

Where I'm stuck is how to break out the EMPLOYEE_ACCT_TYPE into columns with each account type values listed with it's balance. Thanks in advance.

Tony
  • 9,672
  • 3
  • 47
  • 75
Susan
  • 1,822
  • 8
  • 47
  • 69
  • re format the tables to have a person table and a acct table with acct type and person id being the compound primary key for the account table – Laurence Burke Dec 22 '11 at 14:54
  • @Susan you need to provide more info. Do you have several records for each employee? Each with a different account type? – aF. Dec 22 '11 at 14:55
  • 2
    what RDBMS are you using?, do you have a fixed number of `EMPLOYEE_ACCT_TYPE` or do you want your query to be dynamic? – Lamak Dec 22 '11 at 14:57
  • @Susan, I notice you have used SSRS in the past. Do you need to format this data in pure SQL, or can you use SSRS? –  Dec 22 '11 at 15:00
  • A comment on your table design: shouldn't you record the *transactions* against an account then sum them to get the balance, rather then storing the balance itself? – Tony Dec 22 '11 at 15:00
  • 1
    If you're presenting it through a reporting tool that supports cross tabulations then you can pivot the data in the report. – ConcernedOfTunbridgeWells Dec 22 '11 at 15:05
  • Yes, I can use SSRS but I am a rank beginner. In fact, that is the way I would like to proceed. The actual project requires that the user of the report can click on the employee and drill down to a completely different report about that employee. I've been workking through a tutorial on how to pass those parameters to a sub-report. – Susan Dec 22 '11 at 16:03
  • The dataset is the result from a query (actually stored procedure). – Susan Dec 22 '11 at 16:04
  • I do have mutliple records for each employee with one for each account type that includes the balance. – Susan Dec 22 '11 at 16:05

2 Answers2

1

What you are trying to do is a called a Pivot. Some systems (e.g. SQL Server) have native support for this in SQL, but only if you know the number of columns in advance (i.e. you would have to hard-code the account types into the SQL). Other systems don't support pivoting natively (e.g. MySQL) so you would need to write a stored procedure or some dynamic SQL to do it.

Since you don't mention what DBMS you are using, that's about as specific as I can get.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • 1
    You can avoid the requirement for hard coded column names by using a bit of dynamic code http://stackoverflow.com/questions/2922797/t-sql-pivot-possibility-of-creating-table-columns-from-row-values – billinkc Dec 22 '11 at 15:26
  • This was gr8 and really fun too ... ty – Susan Dec 23 '11 at 15:59
0

It sounds to me like you need to do some serious normalization, first. Break employee_account data out into its own table:

table: employee_account_data

EMPLOYEE_ACCT_ID int
EMPLOYEE_ACCT_TYPE varchar(15)
EMPLOYEE_ACCT_BALANCE decimal

You'll also need a bridge table, since many employees can have many accounts (many to many):

table: employee_account_lookup

EMPLOYEE_ID int
EMPLOYEE_ACCT_ID int

This way, you won't be repeating employee_name for each account type (as I suspect you are now). If you really wanted to normalize well, you could also create a table to hold the different Employee Account Types. That way you wouldn't have to worry about someone mispelling "Checking" or "Savings" on data entry.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • Note that the OP says dataset, not table - a dataset can be the result of querying normalised tables. –  Dec 22 '11 at 15:09
  • Yes, that is correct. It is the result of querying normalized tables. – Susan Dec 22 '11 at 16:01
  • @Susan, can you post the table structures used to create the dataset? – Aaron Dec 22 '11 at 16:04
  • This is just a simplified representation of the problem I'm trying to solve. I can provide the data structures and the exact problem, but it will take awhile to get back to you because I want to check and be sure that there is no security issues around sharing this information. – Susan Dec 22 '11 at 16:15