asked on

SQL Syntax

Hi All,

This should be fairly simple. Using SQL 2008 R2 pivot function, how can i take the following query which displays the sample data below and piviot it to show the pivoted data below:

QUERY
SELECT
DataId
,JunkID
,Amount
,Date
FROM table1 t1
inner join table2 t2 on t1.DataId = t2.DataID

DATA DISPLAYED
DataId      JunkId      Amount      Date
Data1      Junk      100.36      201101
Data1      Junk      101.36      201102
Data1      Junk      102.36      201103
Data1      Junk      103.36      201104
Data1      Junk      104.36      201105
Data1      Junk      105.36      201106
Data1      Junk      106.36      201107
Data1      Junk      107.36      201108
Data1      Junk      108.36      201109
Data1      Junk      109.36      201110
Data1      Junk      110.36      201111
Data1      Junk      111.36      201112
Data1      Junk      112.36      201201
Data1      Junk      113.36      201202
Data1      Junk      114.36      201203
Data1      Junk      115.36      201204
Data1      Junk      116.36      201205
Data1      Junk      117.36      201206
Data1      Junk      118.36      201207
Data1      Junk      119.36      201208
Data1      Junk      120.36      201209
Data1      Junk      121.36      201210
Data1      Junk      122.36      201211
Data1      Junk      123.36      201212

PIVOT Data
DateID      JunkID      Jan-11      Feb-11      Mar-11      Apr-11      May-11      Jun-11      Jul-11      Aug-11      Sep-11      Oct-11      Nov-11      Dec-11      Jan-12      Feb-12      Mar-12      Apr-12      May-12      Jun-12      Jul-12      Aug-12      Sep-12      Oct-12      Nov-12      Dec-12
Data1      Junk      100.36      101.36      102.36      103.36      104.36      105.36      106.36      107.36      108.36      109.36      110.36      111.36      112.36      113.36      114.36      115.36      116.36      117.36      118.36      119.36      120.36      121.36      122.36      123.36
Pivot-data.txt

MoreThanDoubled

ASKER

As an added note, i will also need the query to written in such a way that i can use in pull the reults into a grid.

Thanks!

Mark Wills

Well, most reporting type tools have a matrix / tablix / crosstab style of control that can do the pivot for you.

The challenge you have with this style of pivot query from SQL server is having predictive column names so you can manipulate in your end-tool (be it report or form or whatever is the grid)

Normally we might seperate out the year and that way the columns will always be Jan-Dec and so, are predictable and therefore subsequently selectable.

And the pivot function in SQL also needs to know the column names, so that too can be another potential issue.

Otherwise, no major hurdles other than converting the 6 digit YYYYMM into a date so we can get the names. Interesting to see a column named date that isnt a date though...

Now, that column format, if we can make it a bit more like a "known" date format then we dont have to manipulate too much...

e.g.

select *
from   (SELECT DataId,JunkID,Amount,convert(datetime,Date+'01') as date 
        FROM table1 t1
        INNER JOIN table2 t2 on t1.DataId = t2.DataID) src
pivot
       (sum(amount) for date in ([Jan 2011],[Feb 2011],[Mar 2011],[Apr 2011],[May 2011],[Jun 2011],[Jul 2011],[Aug 2011],[Sep 2011],[Oct 2011],[Nov 2011],[Dec 2011]
                                ,[Jan 2012],[Feb 2012],[Mar 2012],[Apr 2012],[May 2012],[Jun 2012],[Jul 2012],[Aug 2012],[Sep 2012],[Oct 2012],[Nov 2012],[Dec 2012]) ) pvt

Open in new window

or to get you exact column names, would have to manipulate a little more :

select *
from   (SELECT DataId,JunkID,Amount,left(date,4)+'-'+left(datename(month,convert(datetime,Date+'01')),3) as date
        FROM table1 t1
        INNER JOIN table2 t2 on t1.DataId = t2.DataID) src
pivot
       (sum(amount) for date in ([2011-Jan],[2011-Feb],[2011-Mar],[2011-Apr],[2011-May],[2011-Jun],[2011-Jul],[2011-Aug],[2011-Sep],[2011-Oct],[2011-Nov],[2011-Dec]
                                ,[2012-Jan],[2012-Feb],[2012-Mar],[2012-Apr],[2012-May],[2012-Jun],[2012-Jul],[2012-Aug],[2012-Sep],[2012-Oct],[2012-Nov],[2012-Dec]) ) pvt

Open in new window

but think the best way is to remove year if at all possible...

select *
from   (SELECT DataId,JunkID,Amount,left(date,4) as yr, left(datename(month,convert(datetime,Date+'01')),3) as mth
        FROM table1 t1
        INNER JOIN table2 t2 on t1.DataId = t2.DataID) src
pivot
       (sum(amount) for mth in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) ) pvt

Open in new window

You can read about the pivot a bit more in a couple of Articles :

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html

Hope that helps...

MoreThanDoubled

ASKER

Mark,

I really appreciate the quick response along with the options you gave. I like the idea of the last option but for some reason its not spreading the numbers across the months, instead its adding the two years worth of data together and placing it in the Nov month.

for example:
Jan Feb.....Nov
Null NULL 8428 (represents the sum)

Any idea why this is happening?

MoreThanDoubled

ASKER

There is something wrong with this piece of code: left(datename(month,convert(datetime,srd.FiscalPeriod+'01')),3) as mth

Mark Wills

It depends on the content and datatype of FiscalPeriod.

If it is numeric, then it will be trying add '01' rather than append to it.

If it is a date, then we need to do something different again.

I had assumed a "string" but looking at your results and comments above it is sounding more like an number. So we need to tell it to concatenate, not sum.

If the content is really something like '201109' and it is numeric then

left(datename(month,convert(datetime,left(srd.FiscalPeriod,6)+'01')),3) as mth

should work. So, give that a try, and let me know if there is still a problem, and if so, what the datatype is and some sample contents.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial