March 13, 2009 at 11:34 am
Hi
I am performing a count on my SQL table but I need the results putting into a pivot table
At the moment I am running the count in the following way:
select postcode,district,age_band,ASH_GENDER,TEL_TYPE,count(*) AS RECS
INTO ASHLEY.DBO.PIVOT_BREAKDOWN
from ASHLEY.DBO.DATA_SET
group by postcode,district,age_band,ASH_GENDER,TEL_TYPE
order by postcode,district,age_band,ASH_GENDER,TEL_TYPE
I would then normally put this in Excel and create a pivot table using Excel - But this query produces too many rows for my excel version...
So I then relised that I could potentially do this directly in SQL. I have tried using the online help but I am getting no-where (Its not very easy to understand)
Anyway - I need my results to look something similar to the following:
Postcode|District|Age Band 18-24 (No of Recs)|Age Band 25-30 (No of Recs)|Gender Male (No of Recs)|Gender Female (No of Recs)|Telephone Type (Broken Down by Landline & Mobile)
AB1 1AB|AB1|5|6|7|4|Landline
AB1 2AB|AB1|7|4|4|7|Mobile
AB1 3AB|AB1|5|3|4|4|Landline
AB1 4AB|AB1|8|2|7|3|Landline
Hope someone can help
Thanks in advance
March 13, 2009 at 12:08 pm
Can you provide the definition of the underlying table, and some sample data to insert into it?
There are a couple of ways to get what you want, but I need to see where it's starting from.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2009 at 12:17 pm
Generically - break this into 2 parts:
1. write the SELECT statement that does the PIVOT correctly.
2. once 1. outputs correctly - wrap the statement from one into a derived table and use a SELECT..INTO structure.
The end result would look something like:
SELECT *
into My NewTable
from
( SELECT myPivotselect statement) g
and voila - no ugly dynamic SQL statements to build a dynamic CREATE TABLE/dynamic column names, etc...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply