David11011
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Thank you Thank you! You guys gave me exactly what I was looking for. UNPIVOT works great.
If I run this command:
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:
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"
If I run this command:
SELECT PHSMRANGE, DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003, DOLLARVALUES_004, DOLLARVALUES_005
FROM PHDOLLARS
WHEREPHSMRANGE like '0502C200'
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
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]
(case substring([Shipping Zone] , 5, 2)
when 'C2' then 'Zone 45'
when <insert code here> then <insert zone here>
end) as [Shipping zone]
ASKER
Thanks for all of your help. That's exactly what I needed
ASKER
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.