?
Solved

re-arrange data from sql select statement

Posted on 2011-09-08
7
Medium Priority
?
303 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
[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
  • 3
7 Comments
 
LVL 5

Accepted Solution

by:
DavidMorrison earned 1200 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 800 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 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