Creating a matrix with to compare same period /yea

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

Creating a matrix with to compare same period /yea

Author

Topic

ulfber
Starting Member

3 Posts

Posted - 2014-05-21 : 15:06:03

Hi All
having a problem with no solution at hand:
Reading data in a YYYYMM for which it has a data value (Forecast)
so data in:
201201 10
201202 5
all up to 201212
201301 11
201302 6

I would like to create a matrix to a screen that does :

Month -> 01 02 03
2012 10 5
2013 11 6

Tried group by, tried everything, i think

The final solution is to create another file with one record per year.

Any help is appreciated

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-21 : 16:15:09

Not quite following. Please post DDL/DML* and the expected output. use code tags around any formatted text or code:

*DDL/DML:
executable statements like:

 
[code]
--DDL
create table #table1 (col1 int, col2 varchar(10))

--DML
insert #table1 (col1, col2)
values (1, '1'),(2,'2'),(3,'3')

--expected output:
col1        col2
----------- ----------
1           1
2           2
3           3
[/code]

Be One with the Optimizer
TG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-21 : 16:20:13

Is the format of the data for 2013 different from those of 2012? For 2012 you have six character that has year and month, for 2013, you have only year. Assuming 2013 also has month portion, use pivot operator, e.g., like this.

SELECT * FROM
(
SELECT
	LEFT (YYYYMM,4) AS [Year],
	RIGHT(YYYYMM,2) AS [Month],
	DataValue
FROM YourTable
) s
PIVOT (MAX(DataValue) FOR [Month] IN ([01],[02],[03]))P

Not sure if your data types are char/varchar/int etc. or something else. This assumes varchar(nn) for the Period. Also, fill in other months that you need as columns

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-21 : 16:51:59

If data is int then this would work:


select period/100                 as [year]
       ,period-((period/100)*100) as [month]

Be One with the Optimizer
TG

ulfber
Starting Member

3 Posts

Posted - 2014-05-21 : 18:06:02

James K I Made a FUPA and updtated the YYYYMM In my original Post. (No they are not different, and I have record for each month in the year)
You say use pivot operator, could you elaborate???
I do this SQL and it gives me a total per year, whereas I would like "One record per year"
SELECT substr(char(ffyfpe),1,4),ffprdc ,sum( FFPDMD ) FROM sroffi
WHERE ffprdc = 'BGL 97117' GROUP BY substr(char(ffyfpe),1,4),
ffprdc ORDER BY substr(char(ffyfpe),1,4), ffprd

TG I do not see anything you post after ":"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-22 : 07:39:03

quote:
Originally posted by ulfber

James K I Made a FUPA and updtated the YYYYMM In my original Post. (No they are not different, and I have record for each month in the year)
You say use pivot operator, could you elaborate???
I do this SQL and it gives me a total per year, whereas I would like "One record per year"
SELECT substr(char(ffyfpe),1,4),ffprdc ,sum( FFPDMD ) FROM sroffi
WHERE ffprdc = 'BGL 97117' GROUP BY substr(char(ffyfpe),1,4),
ffprdc ORDER BY substr(char(ffyfpe),1,4), ffprd

TG I do not see anything you post after ":"

There must be something not quite right with your browser/reader because I am able to see TG's code, and I had posted sample code using the PIVOT operator. Here it is again, without using any code formatting blocks:

SELECT * FROM
(
SELECT
LEFT (YYYYMM,4) AS [Year],
RIGHT(YYYYMM,2) AS [Month],
DataValue
FROM YourTable
) s
PIVOT (MAX(DataValue) FOR [Month] IN ([01],[02],[03]))P

See more about the PIVOT operator here: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

ulfber
Starting Member

3 Posts

Posted - 2014-05-22 : 11:52:16

Thanks James K
I will give that a try,
Still cant see TG's code. One would think that would be nice :-)
HTML is OFF
Forum Code is ON
It says to the side in this message...
Oh well I figure it out

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-22 : 13:04:52

Odd - never heard of someone not being able to view portions of a post. What device (and browser) are you using to view this forum?

EDIT:
this is all my post said:

If data is int then this would work:
select period/100 as [year], period-((period/100)*100) as [month]

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources