Link to home
Start Free TrialLog in
Avatar of David11011
David11011Flag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of DavidMorrison
DavidMorrison
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David11011

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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"
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]

Thanks for all of your help. That's exactly what I needed