Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 20th, 2003, 06:16 AM
Authorized User
 
Join Date: Jun 2003
Location: Edinburgh, , United Kingdom.
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default Turn a table on its side

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
  #2 (permalink)  
Old August 20th, 2003, 06:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Which database are you using? Makes a big difference with problems like this.
  #3 (permalink)  
Old August 20th, 2003, 06:39 AM
Authorized User
 
Join Date: Jun 2003
Location: Edinburgh, , United Kingdom.
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just a locally created one
  #4 (permalink)  
Old August 20th, 2003, 06:42 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Regards
Owain Williams
  #5 (permalink)  
Old August 20th, 2003, 06:43 AM
Authorized User
 
Join Date: Jun 2003
Location: Edinburgh, , United Kingdom.
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default

lol - oops SQLsvr 2000
  #6 (permalink)  
Old August 20th, 2003, 06:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

LO, that's right Owain.
  #7 (permalink)  
Old August 20th, 2003, 06:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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?
  #8 (permalink)  
Old August 20th, 2003, 06:51 AM
Authorized User
 
Join Date: Jun 2003
Location: Edinburgh, , United Kingdom.
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #9 (permalink)  
Old August 20th, 2003, 06:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #10 (permalink)  
Old August 20th, 2003, 07:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
turn off the autocomplete for form fields surya CSS Cascading Style Sheets 3 September 16th, 2008 12:53 AM
Search - Turn Off All Sites druk BOOK: Beginning SharePoint 2007: Building Team Solutions with MOSS 2007 ISBN: 978-0-470-12449-9 1 December 18th, 2007 12:49 PM
cookie turn off swagatika ASP.NET 1.0 and 1.1 Basics 2 March 15th, 2006 09:25 AM
How can I turn off Warning information? Brian263 Access 2 March 23rd, 2004 09:11 AM
to turn Calculation OFF for all except one column alienscript Excel VBA 1 December 17th, 2003 09:01 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.