Rearrange data - 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

General SQL Server Forums

New to SQL Server Programming

Rearrange data

Author

Topic

nkpriya
Starting Member

34 Posts

Posted - 2011-06-17 : 17:14:08

Need to rearrange the data:
I have 12 tables - 1 table for each manager.
Each table has same number of columns with same names:
Manager, Worktype, Jan Hrs, Feb Hrs, Mar Hrs, Apr Hrs, May Hrs, Jun Hrs, Jul Hrs, Aug Hrs, Sep Hrs, Oct Hrs,nov hrs, dec hrs

I need to rearrange data something like this:
Manager, worktype, MonthName, No.of Hrs
Data like
testmgr,testworktype,Jan,10
testmgr,testworktype,Feb,10

I tried to use Union All - but I got system resource exceeded as I am using more than 16 joins.

Please help.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-17 : 21:12:57

>> I tried to use UNION ALL - but I got system resource exceeded as I am using more than 16 joins. <<

That ought to be clue that you have fundamental problems :) Do you have any idea what Normalization means?

>> I have 12 tables - 1 table for each manager. <<

this design flaw is called “attribute splitting”; you should have only one table for Managers. Think: would you split your Personnel table on “sex_code” and have “Male_Personnel” and “:female_Personnel: tables? NO!

>> manager_id, work_type, jan_hrs, feb_hrs, mar_hrs, apr_hrs, may_hrs, jun_hrs, jul_hrs, aug_hrs, sep_hrs, oct_hrs, nov_hrs, dec_hrs <<

The second design flaw is called a repeated group. The month/hrs are values and not attributes as you have incorrectly modeled them. Here is a much better design; move your data over and drop the mess y9u have now.

CREATE TABLE ManagerMonthHours
(mgr_id INTEGER NOT NULL
REFERENCES Managers (mgr_id),
work_type CHAR(5) NOT NULL
CHECK (..),
work_month CHAR (10) NOT NULL
CHECK (work_month LIKE '[12][09][0-9][0-9]-[01][0-9]-00'),
work_hrs DECIMAL (5,2) NOT NULL);

The work_month uses the MySQL convention of “yyyy-mm-00” to show month with a double day field in the ISO-8601 date format. It is not ANSI/ISO Standard but works nicely with them when you sort and search.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-18 : 06:39:53

You can use unpivot to get the data in the format you are looking for. I would do it one table at a time and insert into your target table.


insert into yourTargetTable
select
   *
from
   OneOfYourCurrentTables
unpivot
( hrs for month_name in ([Jan Hrs],[Feb Hrs], [Mar Hrs],...[Dec Hrs]))U

nkpriya
Starting Member

34 Posts

Posted - 2011-06-20 : 11:09:34

I have one table for one manager means - I have sharepoint lists one for each manager. So, I am pulling the lists to microsoft access thru linked table as I need live data. From microsoft access, I am pulling data to SSRS where I need to put this query.

Will try unpivot to join the tables and let you know. Thanks

nkpriya
Starting Member

34 Posts

Posted - 2011-06-20 : 11:45:19

I tried Unpivot and I got SQL Execution Error as Syntax error:

SELECT [Sr GTM], [GTM or Sr Mgr Name], MonthName, Hrs AS NumberofHours FROM
(SELECT [Sr GTM],[GTM or Sr Mgr Name],[Jan Hrs],[Feb Hrs]
FROM Query) AS P
Unpivot
(Hrs FOR [MonthName] IN ([Jan Hrs],[Feb Hrs])) AS Unpivot

Table name - Query Table has columns as [Sr.GTM], [GTM or Sr Mgr Name], [Jan Hrs],[Feb Hrs]

I need new table with columns:
[Sr.GTM], [GTM or Sr Mgr Name], [MonthName],[NumberofHours]

Please help. Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-20 : 15:08:28

It would have been nice if you included the errors you received. But I see two syntax errors:
- You say your table [query] has a column: [Sr.GTM] but your statement references: [Sr GTM]
- you can't use "Unpivot" as your table alias because it is a key word. Change that to something else.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources