Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Syntax

Posted on 2012-04-12
6
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

670 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