Solved

SQL Syntax

Posted on 2012-04-12
6
283 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
ID: 37839468
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
ID: 37841533
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
ID: 37843545
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:MoreThanDoubled
ID: 37843570
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
ID: 37844682
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
ID: 37853604
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

14 Experts available now in Live!

Get 1:1 Help Now