Solved

re-arrange data from sql select statement

Posted on 2011-09-08
7
248 Views
Last Modified: 2012-05-12
I have a table that looks like this


      A      B     C

1   A2    B2   C2

2   A3    B3   C3

3  B4    B4   C4


I want to do a select statement that displays the data like this



        1      2      3

A     A1   A2    A3

B    B1    B2    B3

C    C1    C2    C3
 

The reason why i need this is because there is 200 columns in this table and only 8 rows.
I need to be able to print it on portrait 8.5x11 sheets of paper. If I print the table as is, it will be waaay too wide to print on a piece of paper.

This is a pricing sheet for our sales people to use to quote products for phone customers. I just need to get this info out in a legible format for them to read.

If there is a better way that doesn't cost any more money I'm open to suggestions.

Thanks
0
Comment
Question by:David11011
  • 3
  • 3
7 Comments
 
LVL 5

Accepted Solution

by:
DavidMorrison earned 300 total points
ID: 36505722
Hi David,

I think what you're looking for is the UNPIVOT command in sql

http://msdn.microsoft.com/en-us/library/ms177410.aspx

from there you can whack it into an ssrs report or even excel


As a side note if the table has 8 rows and 200 columns I'd challenge the design as a whole as it would appear that it could use another look.


Thanks

Dave
0
 
LVL 2

Author Comment

by:David11011
ID: 36506248
Dave,

I appreciate your post. I've done a little research on the PIVOT statement and it looks like it requires some sort of aggregation, some examples would be SUM, AVG, and MAX. I don't want  to aggregate or calculate any of the data. I just need to re-arrange the raw data to be displayed in a better format.

And yes, I agree this database is a mess. But I didn't write it. It's the software that the company purchased so I'm stuck with what I've got.
0
 
LVL 5

Assisted Solution

by:25112
25112 earned 200 total points
ID: 36507654
are all the 200 columns same data type..?

pl follow the following example logic.. 12 rows become 12 columns...
CREATE TABLE #test
(
LineNum bigint,
DateBucket varchar(8),
TotalOrders float,
TotalItems float,
TotalItem1 float,
TotalItem2 float,
TotalItem3 float,
AverageCost float,
AveragePrice float,
AverageProfit float
);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (1, 'Jun 2010', 100.0, 220.0, 53.0, 72.0, 99.0, 1204.0, 2468.0, 1122.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (2, 'Jul 2010', 120.0, 300.0, 54.0, 73.0, 98.0, 1208.0, 2470.0, 1123.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (3, 'Aug 2010', 130.0, 280.0, 55.0, 74.0, 97.0, 1212.0, 2472.0, 1124.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (4, 'Sep 2010', 140.0, 190.0, 56.0, 75.0, 96.0, 1216.0, 2474.0, 1125.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (5, 'Oct 2010', 150.0, 250.0, 57.0, 76.0, 95.0, 1220.0, 2476.0, 1126.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (6, 'Nov 2010', 160.0, 260.0, 58.0, 77.0, 94.0, 1224.0, 2478.0, 1127.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (7, 'Dec 2010', 170.0, 200.0, 59.0, 78.0, 93.0, 1228.0, 2480.0, 1128.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (8, 'Jan 2011', 180.0, 210.0, 60.0, 79.0, 92.0, 1232.0, 2482.0, 1129.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (9, 'Feb 2011', 190.0, 230.0, 61.0, 80.0, 91.0, 1236.0, 2484.0, 1130.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (10, 'Mar 2011', 200.0, 270.0, 62.0, 81.0, 90.0, 1240.0, 2486.0, 1131.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (11, 'Apr 2011', 210.0, 240.0, 63.0, 82.0, 89.0, 1244.0, 2488.0, 1132.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (12, 'May 2011', 220.0, 290.0, 64.0, 83.0, 88.0, 1248.0, 2490.0, 1133.0);

SELECT 'Before PIVOT'
SELECT * from #test

SELECT 'After PIVOT'
SELECT ItemDescription as ItemValue,
[1] as Month1, [2] as Month2, [3] as Month3, [4] as Month4, [5] as Month5, [6] as Month6, [7] as  Month7, [8] as Month8, [9] as Month9, [10] as Month10, [11] as Month11, [12] as Month12
FROM
(SELECT v.ItemDescription, v.ItemValue, b.id
FROM
	 (
	 SELECT DateBucket,  ItemDescription,  ItemValue 
	 FROM (SELECT DateBucket,  
	  TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit 
	  FROM #Test) p 
	 UNPIVOT 
 (ItemValue FOR ItemDescription IN
	  (TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit) 
	 ) AS unpvt 
	) as v
	JOIN (
	 select distinct top 100 percent linenum as id, DateBucket
	 from #Test
	 order by linenum) b on v.DateBucket=b.datebucket
) AS SourceTable
	PIVOT
	(
	 MIN(ItemValue)
	 FOR id IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
	) AS PivotTable

DROP TABLE #test

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36509054
Yes sorry I should have stated that it requires an aggregation, however the way you write the sql means you're doing an aggregate over a single value.  If you look at the examples in the msdn it should steer you in the right direction
0
 
LVL 2

Author Comment

by:David11011
ID: 36511678
Thank you Thank you!  You guys gave me exactly what I was looking for. UNPIVOT works great.

If I run this command:
SELECT PHSMRANGE, DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003, DOLLARVALUES_004, DOLLARVALUES_005 
FROM PHDOLLARS 
WHEREPHSMRANGE like '0502C200'

Open in new window

It returns these results:

PHSMRANGE      DOLLARVALUES_001      DOLLARVALUES_002      DOLLARVALUES_003      DOLLARVALUES_004      DOLLARVALUES_005
0502C200                            945                                  967                                 976                                     990                                     1013


Then when I use the UNPIVOT command like you savvy young men suggested:
SELECT [Shipping Zone], [Weight], [Price] FROM (select PHSMRANGE AS  [Shipping Zone], DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003, DOLLARVALUES_004, DOLLARVALUES_005 FROM 
(SELECT PHSMRANGE, DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003, DOLLARVALUES_004, DOLLARVALUES_005 FROM PHDOLLARS WHERE PHSMRANGE = '0502C200') p ) p
UNPIVOT
([Price] FOR [Weight] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003, DOLLARVALUES_004, DOLLARVALUES_005))
AS unpvt
GO

Open in new window

I get these reults:

Shipping Zone            Weight                  Price
0502C200        DOLLARVALUES_001      945
0502C200        DOLLARVALUES_002      967
0502C200        DOLLARVALUES_003      976
0502C200        DOLLARVALUES_004      990
0502C200        DOLLARVALUES_005      1013



This is perfect. But, is there a way to change the way that the the Shipping Zones column is displayed?
example:

IF substring([Shipping Zone] , 5, 2) = "C2" then [Shipping Zone] == "Zone 45"
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36511875
glad to help, as for your other question just use a CASE statement something like below in your select statement


 (case  substring([Shipping Zone] , 5, 2)  
              when 'C2' then 'Zone 45'
              when <insert code here> then <insert zone here>
           end) as [Shipping zone]

0
 
LVL 2

Author Closing Comment

by:David11011
ID: 36511888
Thanks for all of your help. That's exactly what I needed
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now