Turn a table on its side

nbryson · August 20th, 2003, 06:16 AM

Hi

I am looking for suggestions for a way I can turn a table on its side. Here is a snapshot of the the current result set :-
value number months band
--------------------- ----------- ----------- ----------
5648269.6956 1806 7 Band1
5797476.8052 2133 6 Band1
6458146.4473 2165 5 Band1
6063204.5223 2006 4 Band1
7408796.9681 2292 3 Band1
7145527.3191 2201 2 Band1
5242624.9785 105 7 Band2
4739693.8732 99 6 Band2
5471446.2941 120 5 Band2
6193100.6250 132 4 Band2
5763959.0102 125 3 Band2
5648435.3923 115 2 Band2

The output I am looking to get it to have each band as a row. Then to have columns Month1 number, Month1 value, Month2 number Month2 value etc.

The month numbers are always 2-7 and there and only Band1 to Band4.

Output e.g.
M1no, M1val, M2no, M2val, M3no, M3val
Band1
Band2
Band3
Band4

Any help would be appreciated.

Nickie

pgtips · August 20th, 2003, 06:36 AM

Which database are you using? Makes a big difference with problems like this.

nbryson · August 20th, 2003, 06:39 AM

Just a locally created one

owain · August 20th, 2003, 06:42 AM

I think 'pgtips' want's to know what RDBMS you are using, SQL Server, Oracle, Access, etc.

Regards
Owain Williams

nbryson · August 20th, 2003, 06:43 AM

lol - oops SQLsvr 2000

pgtips · August 20th, 2003, 06:43 AM

LO, that's right Owain.

pgtips · August 20th, 2003, 06:45 AM

Sorry for more Q's when you just want an answer, but is this the whole resultset or do you have multiple records for each Band/Month? If there are multiples do you want to sum them?

nbryson · August 20th, 2003, 06:51 AM

No probs

Here is the full results set :-

value number months band
--------------------- ----------- ----------- ----------
5648269.6956 1806 7 Band1
5797476.8052 2133 6 Band1
6458146.4473 2165 5 Band1
6063204.5223 2006 4 Band1
7408796.9681 2292 3 Band1
7145527.3191 2201 2 Band1
5242624.9785 105 7 Band2
4739693.8732 99 6 Band2
5471446.2941 120 5 Band2
6193100.6250 132 4 Band2
5763959.0102 125 3 Band2
5648435.3923 115 2 Band2
2733406.5917 19 2 Band3
3718481.1394 24 3 Band3
2995971.4593 20 4 Band3
2996010.9821 19 5 Band3
2896566.7939 21 6 Band3
3468992.2668 22 7 Band3
3635406.2400 11 2 Band4
1920527.9207 5 3 Band4
3242817.8229 9 4 Band4
3986865.8893 9 5 Band4
1802750.4400 5 6 Band4
1264799.4689 4 7 Band4

There will only be Bands 1-4 and within there bands there are only Months 2-7 which I want to have as columns stating the number and value. No summation is required.

Thx

pgtips · August 20th, 2003, 06:54 AM

OK, at first glance it seems like a variation on this problem http://p2p.wrox.com/topic.asp?TOPIC_ID=2048.

Give me a few mins to create the data and play around and I'll get back to you.

rgds
Phil

pgtips · August 20th, 2003, 07:08 AM

Yep that tactic works here too:

Code:

SELECT band, 
    SUM(CASE months WHEN 2 THEN number ELSE 0 END) AS [M2no],
    SUM(CASE months WHEN 2 THEN [value] ELSE 0 END) AS [M2val],
    SUM(CASE months WHEN 3 THEN number ELSE 0 END) AS [M3no],
    SUM(CASE months WHEN 3 THEN [value] ELSE 0 END) AS [M3val],
    SUM(CASE months WHEN 4 THEN number ELSE 0 END) AS [M4no],
    SUM(CASE months WHEN 4 THEN [value] ELSE 0 END) AS [M4val],
    SUM(CASE months WHEN 5 THEN number ELSE 0 END) AS [M5no],
    SUM(CASE months WHEN 5 THEN [value] ELSE 0 END) AS [M5val],
    SUM(CASE months WHEN 6 THEN number ELSE 0 END) AS [M6no],
    SUM(CASE months WHEN 6 THEN [value] ELSE 0 END) AS [M6val],
    SUM(CASE months WHEN 7 THEN number ELSE 0 END) AS [M7no],
    SUM(CASE months WHEN 7 THEN [value] ELSE 0 END) AS [M7val]
FROM <<yourtable>>
GROUP BY band