Solved

SQL PIVOT Table

Posted on 2010-09-17
24
720 Views
Last Modified: 2012-05-10
What I am trying to do is create a Pivot Table in SQL to mimic that of and Excel Pivot.  See the attached example. Test-Pivot.xls
0
Comment
Question by:matheinjoe
  • 9
  • 8
  • 7
24 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33703841
Are you wanting then the columns to be the cities (e.g., Naples, Miami, etc.) and the row headings to be the the combination of audit category (e.g., Finance, IT, etc.) and month (e.g., January - December) with the value sum totaled under each city?

If you data is structured like the DATA tab of the spreadsheet, then the first task is to unpivot the data so that the months on separate rows and then you can use the PIVOT keyword to pivot on the city names.  If this is a finite/known list of names then you can just hard code the PIVOT statement as normal.

Otherwise, to dynamically generate this based on the actual data, then you will have to use dynamic SQL.

Reference:
Dynamic Pivot Procedure for SQL Server - http:A_653.html
by mark_wills - http:M_4390378.html

If you need more specific assistance, then please provide a sample of the pertinent table structure and data.

Regards,
kevin
0
 

Author Comment

by:matheinjoe
ID: 33703986
I want it to look just like the example in the attached Excel file.  The Data Tab is what creates the PIVOT tab.If I unpivot the data to where it is on seperate rows, then what?  This may be way out of my league.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33704324
Which is why I said to post your data structure and sample of data as it exists in SQL server and can assist you as I explained the process above.  You UNPIVOT to get months out of columns and into row level data and then PIVOT to get the row level data of City name into columns.  The Article I sent you to explains the PIVOT keyword as well as provides a solution for dynamically pivoting data, so once you UNPIVOT your data you can just feed it to the stored procedure and tell it to PIVOT on column containing the City and Voila!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33704343
And yes, there are often times when it is nice to have a consultant or mentor working with you if trying to learn an advanced concept.  It can be very difficult to transfer knowledge in just one forum thread ; however, we do our best here to try and accomplish as much of the concept understanding we can.  Consequently, we don't have your environment, so that doesn't always translate easily without you having to provide lots of detail to us on structure.

So you may not learn it all in this one question, but over time hopefully with asking detailed and very task specific questions, you can compile a very good SQL toolkit / skillset for the things you do on a daily basis.

Regards,
Kevin
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33704828
Please do read mwvisa1's article suggestion. But I just want to give you another hand by showing you the main structure of the query mwvisa1's was trying to explain
As you can see you have to explicitly mention all the cities/town there in your query. You will have to go dynamic if the number of cities varies or are unknown beforehand.
Having said that, I would like to point out that you shouldn't be doing this at the SQL server level. This should be done with a reporting tool (e.g. Reporting Services). SQL Server is not excel, and as such not the same things applies to both.
Also if your table has that design, then you definitively need to reconsider it. You normally don't have months as column names. Good designing approach will save you lots of time and effort in the future.

;with CTE as (

select [Audit Category Description], 

	[Vendor category description],

	p.Mth,

	p.[Value]

from (

	select 	[Audit Category Description], 

		[Vendor category description],

		Jan,

		Feb,

		...

		Dec

	from yourtable

) o

UNPIVOT (Value for Mth in (Jan, Feb, March, ...., Dec)) p

)

select [Audit Category Description],

	Mth,

	[Jacksonville],

	[Miami],

	[Naples],

	... etc

from CTE

PIVOT (sum([Value]) for [Vendor category description] in ([JacksonVille],[Miami],[Naples].... etc )) as p1

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33704935
Exactly. I was hoping to show you with your actual data structure names, but ralmada's post should be pretty clear how to translate to your actual table structure.  Note the part in the CTE is the UNPIVOT which if you needed dynamic SQL is the portion of code I would make into a view and then you can use the view to do the dynamic SQL or pass it to Mark's stored procedure in the article.
0
 

Author Comment

by:matheinjoe
ID: 33716987
Ok, here is the unpivoted data, as well as the code I am using to pull it.  I have attempted to use the example from ralmada above, but I am running into issues.  I do realize that ralmada was UNPIVOTING, so I did not use that piece.
(SELECT   

    CITY,           

    DEPT,     

    MO,   

    SUM(Total) AS 'TOTAL'       

    

  FROM (        

          

  SELECT         

       

  C.shortdesc as  City,       

  D.shortdesc as 'Dept',  

  MONTH(TransactionDate) AS 'MO'

      

  isnull(        

     

  SUM(CASE WHEN TransTypeCode = 2 THEN          

  CASE WHEN CreditDebit= 'C' THEN TransAmt ELSE (TransAmt *-1) END ELSE 0 END )        

  ,0)   AS 'TOTAL'         

          

  FROM TransTbl with (NOLOCK)  

LEFT JOIN  LUCity C with (NOLOCK) on CityID = C.ID   

LEFT JOIN  LUDept D with (NOLOCK) on DeptID = D.ID

     

 WHERE 

 DATEDIFF(Year, TransactionDate, '9/30/2010') = 0  

 AND( '9/30/2010' NOT IN ('M') OR DATEDIFF(MONTH,TransactionDate,'9/30/2010') = 0) 

  

 

GROUP BY   

       

  C.shortdesc, 

  D.shortdesc,            

  MONTH(TransactionDate) 

  

  ) TBL     

  

  GROUP BY    



 City, 

 Dept,        

 Total,    

  MO)

Open in new window

Test1.xls
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33717287
What issues are you running into?  You are correct that since this query gets the data in vertical form, you don't need to UNPIVOT, but you will want the portion of the example that was doing the PIVOT on CITY.  Is that where you are having trouble?  Is so, please post details on that difficulty.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33717673
As indicated by mwvisa, your new query should look like this.
Please also note that I'm suggesting a new way to filter the whole month which provided you have your table indexed on Transactiondate, will perform faster. It is never a good practice to include a function on the left side of the equation like
WHERE
DATEDIFF(Year, TransactionDate, '9/30/2010') = 0  
AND( '9/30/2010' NOT IN ('M') OR DATEDIFF(MONTH,TransactionDate,'9/30/2010') = 0)
Instead you should do something like this:
 WHERE  ('9/30/2010' <> 'M') OR
  (Transactiondate >= dateadd(m, datediff(m, 0, '9/30/2010'), 0) and
  Transactiondate <  dateadd(m, datediff(m, 0, '9/30/2010')+1, 0))  
Now this part in your original query confuses me
('9/30/2010' not in ('M')  )
I left it like there, but because it's a constanst that you're comparing, I reordered it so that it will get tested first before, that will also improve your speed, but you might consider removing to make it faster.
The whole query is attached in Item #1 below
Now I guess the problem you're running into is that you have too many cities and you don't know them beforehand. So in that case you will have to use dynamic SQL. See example I've attached in item #2 below.

-- Item #1 ------------- static query

;with CTE as (

SELECT CITY,           

    	DEPT,     

    	MO,   

    	SUM(Total) AS 'TOTAL'       

  FROM (        

	SELECT               

  		C.shortdesc as  City,       

  		D.shortdesc as 'Dept',  

  		MONTH(TransactionDate) AS 'MO'

      		isnull(             

  			SUM(	CASE WHEN TransTypeCode = 2 THEN          

  				CASE WHEN CreditDebit= 'C' THEN TransAmt ELSE (TransAmt *-1) END ELSE 0 END )        

  		,0)   AS 'TOTAL'         

	FROM TransTbl with (NOLOCK)  

	LEFT JOIN  LUCity C with (NOLOCK) on CityID = C.ID   

	LEFT JOIN  LUDept D with (NOLOCK) on DeptID = D.ID

	WHERE 	('9/30/2010' <> 'M') OR 

		(Transactiondate >= dateadd(m, datediff(m, 0, '9/30/2010'), 0) and

		Transactiondate <  dateadd(m, datediff(m, 0, '9/30/2010')+1, 0))  

	GROUP BY C.shortdesc, 

  		D.shortdesc,            

  		MONTH(TransactionDate) 

  ) TBL     

  GROUP BY City, 

 	Dept,        

 	Total,    

  	MO

)

select 	Dept, [Tampa], [Jacksonville], ... all the cities explicitly declared...

from CTE o

pivot (sum([Total]) for City in ([Tampa], [JacksonVille], ... all cities ... )) p



--Item #2 - Dynamic SQL query ----------------------------------------

declare @strSQL varchar(max)

declare @cols varchar(max)



set @cols = stuff((	select distinct '], [ ' + C.shortdesc 

			from transTbl

			left join LuCity C on CityID = c.ID

			WHERE 	('9/30/2010' <> 'M') OR 

			(Transactiondate >= dateadd(m, datediff(m, 0, '9/30/2010'), 0) and

			Transactiondate <  dateadd(m, datediff(m, 0, '9/30/2010')+1, 0))  			

		), 1, 2, '') + ']'



set @strSQL = '

	;with CTE as (

		SELECT CITY,           

	    	DEPT,     

    		MO,   

	    	SUM(Total) AS [TOTAL]       

	  FROM (        

		SELECT               

  			C.shortdesc as  City,       

	  		D.shortdesc as [Dept],  

  			MONTH(TransactionDate) AS [MO]

      			isnull(             

	  			SUM(	CASE WHEN TransTypeCode = 2 THEN          

  					CASE WHEN CreditDebit= ''C'' THEN TransAmt ELSE (TransAmt *-1) END ELSE 0 END )        

	  		,0)   AS [TOTAL]         

		FROM TransTbl with (NOLOCK)  

		LEFT JOIN  LUCity C with (NOLOCK) on CityID = C.ID   

		LEFT JOIN  LUDept D with (NOLOCK) on DeptID = D.ID

		WHERE 	(''9/30/2010'' <> ''M'') OR 

			(Transactiondate >= dateadd(m, datediff(m, 0, ''9/30/2010''), 0) and

			Transactiondate <  dateadd(m, datediff(m, 0, ''9/30/2010'')+1, 0))  

		GROUP BY C.shortdesc, 

  			D.shortdesc,            

  			MONTH(TransactionDate) 

	  ) TBL     

	  GROUP BY City, 

 		Dept,        

	 	Total,    

  		MO

	)

	select 	Dept, ' + @cols + 

	' from CTE o

	pivot (sum([Total]) for City in (' + @cols + ')) p'



exec(@strSQL)

Open in new window

0
 

Author Comment

by:matheinjoe
ID: 33717692
Where do I place this piece of code in from ralamada in my code:

select          City,
      MO,
      '' As Accounting,
      '' As Finance,
      '' As Sales,
      '' As IT,
                     '' As Marketing
from CTE
PIVOT (sum([TOTAL]) for [Vendor category description] in (Accounting, Finance, Sales, IT, Marketing)) as p1
 
0
 

Author Comment

by:matheinjoe
ID: 33717939
Ralmada,

I have tried your code, however the Sum(Total) will not populate in the query results.

Any suggestions?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 250 total points
ID: 33718144
Please post your exact query. I'm not sure which one you've tried.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:matheinjoe
ID: 33718356
Here is my code
;with CTE as (

SELECT CITY,           

    	DEPT,     

    	MO,   

    	SUM(Total) AS 'TOTAL'       

  FROM (        

	SELECT               

  		C.shortdesc as  City,       

  		D.shortdesc as 'Dept',  

  		MONTH(TransactionDate) AS 'MO'

      		isnull(             

  			SUM(	CASE WHEN TransTypeCode = 2 THEN          

  				CASE WHEN CreditDebit= 'C' THEN TransAmt ELSE (TransAmt *-1) END ELSE 0 END )        

  		,0)   AS 'TOTAL'         

	FROM TransTbl with (NOLOCK)  

	LEFT JOIN  LUCity C with (NOLOCK) on CityID = C.ID   

	LEFT JOIN  LUDept D with (NOLOCK) on DeptID = D.ID

	WHERE 	('9/30/2010' <> 'M') OR 

		(Transactiondate >= dateadd(m, datediff(m, 0, '9/30/2010'), 0) and

		Transactiondate <  dateadd(m, datediff(m, 0, '9/30/2010')+1, 0))  

	GROUP BY C.shortdesc, 

  		D.shortdesc,            

  		MONTH(TransactionDate) 

  ) TBL     

  GROUP BY City, 

 	Dept,        

 	Total,    

  	MO

)

select 	Dept, MO,

'' As JACKSONVILLE,

'' As TAMPA,

'' As ORLANDO,

'' As NAPLES,

'' As TALLAHASSEE,

'' As MIAMI

from CTE o

pivot (sum([Total]) for City in (JACKSONVILLE, TAMPA, ORLANDO, NAPLES, TALLAHASSEE, MIAMI )) p

Open in new window

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33718396
Think the problem is :

select       Dept, MO,
'' As JACKSONVILLE,
'' As TAMPA,
'' As ORLANDO,
'' As NAPLES,
'' As TALLAHASSEE,
'' As MIAMI


You have literal '' instead of using JACKSONVILLE, TAMPA, et al as column names which they are after the PIVOT.

select       Dept, MO,
JACKSONVILLE,
TAMPA,
...
MIAMI

Kevin
0
 

Author Comment

by:matheinjoe
ID: 33718413
If I do run the code above without the PIVOT piece I do get some NULL values for the City Field.  I am not sure that will cause any issues.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33718444
If you are worried about NULL's then you would do what you would on any query column: use ISNULL or COALESCE.

select COALESCE(JACKSONVILLE, 0) AS JACKSONVILLE ...
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33718484
first I would try adding brackets there like the below
;with CTE as (

SELECT CITY,           

    	DEPT,     

    	MO,   

    	SUM(Total) AS Total       

  FROM (        

	SELECT               

  		C.shortdesc as  City,       

  		D.shortdesc as 'Dept',  

  		MONTH(TransactionDate) AS 'MO'

      		isnull(             

  			SUM(	CASE WHEN TransTypeCode = 2 THEN          

  				CASE WHEN CreditDebit= 'C' THEN TransAmt ELSE (TransAmt *-1) END ELSE 0 END )        

  		,0)   AS 'TOTAL'         

	FROM TransTbl with (NOLOCK)  

	LEFT JOIN  LUCity C with (NOLOCK) on CityID = C.ID   

	LEFT JOIN  LUDept D with (NOLOCK) on DeptID = D.ID

	WHERE 	('9/30/2010' <> 'M') OR 

		(Transactiondate >= dateadd(m, datediff(m, 0, '9/30/2010'), 0) and

		Transactiondate <  dateadd(m, datediff(m, 0, '9/30/2010')+1, 0))  

	GROUP BY C.shortdesc, 

  		D.shortdesc,            

  		MONTH(TransactionDate) 

  ) TBL     

  GROUP BY City, 

 	Dept,        

 	Total,    

  	MO

)

select 	Dept, MO,

[JACKSONVILLE],

[TAMPA],

[ORLANDO],

[NAPLES],

[TALLAHASSEE],

[MIAMI]

from CTE o

pivot (sum(Total) for City in ([JACKSONVILLE], [TAMPA], [ORLANDO], [NAPLES], [TALLAHASSEE], [MIAMI] )) p

Open in new window

0
 

Author Comment

by:matheinjoe
ID: 33718498
So if one of the cities was St Petersburg, would I need to state that as the following:

select       Dept, MO,
JACKSONVILLE,
TAMPA,
ORLANDO,
NAPLES,
TALLAHASSEE,
MIAMI,
[ST PETERSBURG]
from CTE o
PIVOT (sum([TOTAL]) for CITY in (JACKSONVILLE, TAMPA, ORLANDO, NAPLES, TALLAHASSEE, MIAMI, [ST PETERSBURG] )) p
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33718541
always include the brackets, even for the ones that don't have a space. like Jacksonville, tampa, orlando, etc.
 
0
 

Author Comment

by:matheinjoe
ID: 33718570
I just figured out my issue.  I had the date field set incorrectly.  Disregard my previous question about ST PETERSBURG.

Ralmada/mwvisa1,

Now that I got the PIVOT working, is there a way to create a Month Total and Grand Total within the PIVOT?  How about a calculated field?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33718693
>>Month Total and Grand Total within the PIVOT?  How about a calculated field? >>
Yes it can be done, but like I've mentioned before, this is not to be done at the database level, but at the presentation level with a reporting tool such us reporting services. So basically you feed the query you've created and then customize the report to include a grand total and month total
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33718823
I would agree with ralmada.  Definitely possible in SQL, but you will see if intermingles with your results and could get confused with other data and thus skew your true results if someone adds the totals to the other data and makes some new overstated total to base a decision off of.

If you absolutely need this, you can look at a UNION to bring the other data back in or something like WITH ROLLUP.  Here is a simple example you can play with:
-- Example 1: with rollup, subtotals each level of grouping

;with cte as ( 

   select coalesce(City, 'Totals') as City

        , coalesce(Dept, 'Totals') as Dept

        , coalesce(MO, 'Totals') as MO

        , sum(Total) as Total

   from (

      select 'Tampa', 'Sales', 'JAN', 300.00

      union select 'Tampa', 'IT', 'JAN', 500.00

      union select 'Jacksonville', 'IT', 'JAN', 400.00

   ) as data(City, Dept, Mo, Total)

   group by City, Dept, MO with rollup

)

select *

from cte

pivot (sum(Total) for City in ([JACKSONVILLE], [TAMPA], [TOTALS])) p

;



-- example 2: filtering to only get grand totals

;with cte as ( 

   select coalesce(City, 'Totals') as City

        , coalesce(Dept, 'Totals') as Dept

        , coalesce(MO, 'Totals') as MO

        , sum(Total) as Total

   from (

      select 'Tampa', 'Sales', 'JAN', 300.00

      union select 'Tampa', 'IT', 'JAN', 500.00

      union select 'Jacksonville', 'IT', 'JAN', 400.00

   ) as data(City, Dept, Mo, Total)

   group by City, Dept, MO with rollup

)

select *

from cte

pivot (sum(Total) for City in ([JACKSONVILLE], [TAMPA], [TOTALS])) p

where MO <> 'Totals' OR Dept = 'Totals'

Open in new window

0
 

Author Comment

by:matheinjoe
ID: 33719001
Doesn't seem like it will work with what I am trying to do.  But I thank you guys from helping me out.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33719097
You're very welcome. Here are some links that will help you do this with SSRS. You will understand what I'm talking about when you see how easy it is in Reporting Services.
http://www.lukehayler.com/2010/03/creating-subtotals-in-a-matrix-in-ssrs-20052008/
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e31e09db-4aed-4c59-9cc0-7d90739d9ab9
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

759 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

19 Experts available now in Live!

Get 1:1 Help Now