• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

re-arrange data from sql select statement

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
David11011
Asked:
David11011
  • 3
  • 3
2 Solutions
 
DavidMorrisonCommented:
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
 
David11011Author Commented:
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
 
25112Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
DavidMorrisonCommented:
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
 
David11011Author Commented:
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
 
DavidMorrisonCommented:
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
 
David11011Author Commented:
Thanks for all of your help. That's exactly what I needed
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now