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
Solved

re-arrange data from sql select statement

Posted on 2011-09-08
7
281 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 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