Solved

Un-rotate Table?

Posted on 2012-04-04
3
338 Views
Last Modified: 2012-04-04
I have a table that has fields named similar to:
Company
Employee
Year
Sales_01
Sales_02
.
.  (field names between Sales_03 and Sales_10)
.
Sales_11
Sales_12

What I need is to un-rotate the table so that I have a new (display) field called "month" and the Sales_?? fields separated by month and just called Sales.

So, if my table has data such as this:
Company Employee Year Sales_01 Sales_02 Sales_03 Sales_04 Sales_05 (trimmed out, but you get the idea)
ABC     12345    2011 12345.67 62534.23 98737.32 92873.34 12343.30 

Open in new window


The resulting data will look like this:
Company Employee Year Month Sales
ABC     12345    2011     1 12345.67 
ABC     12345    2011     2 62534.23 
ABC     12345    2011     3 98737.32 
ABC     12345    2011     4 92873.34 
ABC     12345    2011     5 12343.30 

Open in new window


I thought the T-SQL command "UNPIVOT" would work, but I'm not sure how to use it, and how the month field would appear.

TIA
0
Comment
Question by:Clif
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
ID: 37807274
Something like this:
DECLARE @T TABLE (Company VARCHAR(10),Employee INT, [year] INT, Sales01 INT, Sales02 INT, Sales03 INT, Sales04 INT)
INSERT INTO @T SELECT 'ABC',12345,2011,555,444,666,888

SELECT Company, Employee, year, cast(RIGHT(Sales,2) AS INT) AS [Month], SalesAmt
FROM (
SELECT * FROM @T T
UNPIVOT (SalesAmt FOR Sales IN (Sales01, Sales02, Sales03, Sales04)) AS uv
) T1

Open in new window

0
 
LVL 10

Author Closing Comment

by:Clif
ID: 37807492
That was perfect, thanks.
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question