SQL Pivot Table with Dynamic Columns
BlackBerry , SQL - Author:Steve Weyrick at 6:20 PM Add commentsLast week, I was tasked with sending out an email of hourly production data that was to be read on blackberry phones. It started off as something really simple; just sending a plain html table in an email.
Something like this:
Moreover, this turned into something much more complex when I found out that I needed to create a pivot chart which displayed the hourly times as dynamic columns. I had never been asked to do anything like this before, and It took me a little bit out of my comfort zone. However, after finding a ton information on this topic, this task became relativily easy.
How to create a pivot chart with dynamic columns?
<cfquery name="qryPivot" datasource="#nscdb#">
DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(Date as varchar) + ']',
'[' + cast(Date as varchar)+ ']')
FROM vGroupMail
GROUP BY Date
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM vGroupMail
PIVOT
(
MAX(Pac)
FOR [Date]
IN (' + @columns + ')
)
AS p'
EXECUTE(@query)
</cfquery>
And thats it!!! Once you study this code or view someone else's post on the topic, you shouldn't have a problem.
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
http://blog.crowe.co.nz/archive/2005/09/05/250.aspx
Here is the output in an html table.
*******************************************
Mar 25, 2009 at 7:09 PM Thats great! :-) I couldnt find it in russian, so Thank u so much
Aug 19, 2009 at 12:47 PM I am using the same code: DECLARE @query VARCHAR(8000) SET @query = 'SELECT * FROM #PreviousExceptions PIVOT (sum([Previous Months Exceptions])FOR [Month]IN (' + @columns + '))AS p' I get an error: Line 3: Incorrect syntax near '(' I cannot get pass this error. Do you have any suggestions?
Aug 19, 2009 at 1:07 PM @rhonda If you send me your query I will take a look at it. Or you can gchat me at sweyrick. It doesn't look like you posted the entire query, and I can't tell whats going on.
Aug 19, 2009 at 1:23 PM do you have an email I can send it to. The code is pretty long.
Aug 19, 2009 at 1:27 PM sweyrick@gmail.com
Aug 24, 2009 at 12:42 PM I have been searching for this code...forever...awesome work, and thank you for sharing
Oct 28, 2009 at 2:14 AM I created 2 tables 1 st table as csat csat_code csat_ou_cod csat_csaeid cus name CSA00001 ADM00001 6825 CUS08349 CSA00002 ADM00001 6826 CUS08347 CSA00003 ADM00001 6832 CUS08351 CSA02547 ADM00001 6824 CUS04150 table2 as csat_survey surveycode csat_code qns id ans id SUR00001 CSA02547 1 3 SUR00002 CSA02547 4 7 now i want the resut is csat_code csat_case_id qns id(column name) like the row field of qns id ( table2) is cloumn for my solution table. and the number of qns also varied .so i have to create the dynamic header.. plz any one help me? how can i get this solution? Thanks in advance Geetha