Splitting Data into two columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Splitting Data into two columns

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-08-25 : 09:24:59

Hi

I have a partial result set as below:

Fig. 1


Country   Quarter SubRegion  Total
=======   ======= =========  ============
CIS	  2010/1  CEE	     16930147.65
CIS	  2010/2  CEE	     16611147.8

And want to display it as:


Country   Quarter-2010/01   Quarter-2010/01   SubRegion  
=======   ==============    ==============    =========  
CIS	  16930147.65       16611147.8        CEE

How can I do this?

Here is my query so far that returns fig. 1


 select Country, [Quarter], SubRegion, SUM([Forecast Value])
 from Table1 
 where SubRegion in (select SubRegion
		     from table1)
 group by SubRegion, Country, [Quarter]
 order by SubRegion, Country, [Quarter]

Thanks

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-08-25 : 10:14:39

Is it a pivot I need to use? I have tried playing about with an example but no luck:

 select 'Forecast Value' AS Cost_Sorted_By_Quarter, 
		[2010], 
		[2011]
 from	(select [Forecast Value], 
				[Quarter]
		 from   Pivot_DataSource) 
		        AS SourceTable
 PIVOT 
 (
 SUM([Forecast Value]) 
  for [Quarter] IN ([2010], [2011])
  ) AS PivotTable

Returns:

Cost Sorted By Quarter    2010    2011
======================    ====    ====
Forecast Value	          NULL	  NULL

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-08-25 : 10:34:47

Also after I type PIVOT there is no drop down list of database columns coming up ,when manually insert and run it says Incorrect syntax near 'Value'

G

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-08-25 : 11:00:23

I wouldn't do this kind of thing in the database. It's much better to format data in the application or front end.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-08-25 : 11:03:22

Hi

I am only practising and am trying to learn how to use pivot.

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-08-25 : 11:22:15

28 views and no one knows how to use pivot?

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-08-26 : 06:22:58

I now have this working and would like to know how I can total the 2 quarter values from the pivot?

Thanks again


select *
 from (Select [Forecast Value], Quarter, SubRegion
	  from Pivot_DataSource) AS p
PIVOT
(SUM([Forecast Value]) FOR [Quarter] IN ([2010/1], [2010/2])) AS pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources