Solved

SQL Syntax

Posted on 2012-04-12
6
281 Views
Last Modified: 2012-04-17
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
0
Comment
Question by:MoreThanDoubled
  • 3
  • 3
6 Comments
 

Author Comment

by:MoreThanDoubled
Comment Utility
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!
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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 :

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

Hope that helps...
0
 

Author Comment

by:MoreThanDoubled
Comment Utility
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?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:MoreThanDoubled
Comment Utility
There is something wrong with this piece of code: left(datename(month,convert(datetime,srd.FiscalPeriod+'01')),3) as mth
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
How did you go with the above ?

You might also want to read that PIVOT article - it also shows how you can handle potentially unknown columns.

Though, for Dates, I still prefer having  Jan - Dec so any missing periods (if that is possible) will always get a mention - but that is just me.

But the "unknown" columns normally requires a procedural approach and some dynamic SQL

e.g.

declare @cols_in varchar(8000)
declare @sql varchar(8000)

select @cols_in = isnull(@cols_in + ',' , '') + '[' + left([date],6) + ']' from #table1 group by date

set @sql = '
select *
from   (SELECT DataId,JunkID,Amount, left(Date,6) as mth
        FROM #table1 t1) src
      --INNER JOIN table2 t2 on t1.DataId = t2.DataID) src
pivot
       (sum(amount) for mth in ('+@cols_in+') ) pvt'

exec (@sql)

Open in new window


But it is better if you do have predictive column names, so you can map to grids, select on them, group them (like quarters or year to date etc).

The same code as above (but this time with integer [date] and no procedures just a straight SQL query...

select *
from   (SELECT DataId,JunkID,Amount,left(date,4) as yr, left(datename(month,convert(datetime,left(Date,6)+'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



Anyway, that dynamic bit is explained in the article (albeit as a substring, or could have used stuff to remove the comma, or even XML  to concatenate).

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now