December 15, 2011 at 10:02 am
Hi Guys,
I was wondering what the most efficient way is to manipulate the layout of my results from a query.
It currently looks like this....
Date Region Fail
15/12/2011Australia 29
15/12/2011Canada 0
15/12/2011Caribbean33
15/12/2011Central America25
15/12/2011Europe 18
15/12/2011Japan 0
15/12/2011Rest of World54
15/12/2011South America50
15/12/2011US 0
I would like it to be stored like this:
Date Australia Canada Caribbean Central America Europe Japan Rest of World South America US
15/12/2011 29 0 33 25 18 0 54 50 0
Okay that does not show up how it is supposed to, but you get the idea I hope!
I figured it was best to manipulate the dataset at this stage before I start to import it into .R
December 15, 2011 at 10:11 am
Read up on PIVOT in BOL if the values are known, or search this site for Dynamic Pivot if not.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 15, 2011 at 10:14 am
Will take a look!
Is that the most efficient way to go about it or is it worth revising the query?!
Thanks Again!
December 15, 2011 at 10:19 am
It really depends.
Is this a one time need?
Why do you need it that way?
You can just as easily paste the data into an Excel spreadsheet and then copy and "transpose - paste" which would pivot.
You could create a report in SSRS.
There are many possible answers, we'd just need more info on the end goal.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 15, 2011 at 10:29 am
The current query to produce those results is in 3 steps that takes an individual location, groups it into an account and then a region. it scores each location and account based on pre-determined criteria to come up with a pass fail rate for each region....
I would like to add a single row to a table each week with the date and the percentage 'score' for each region, this way over time I can build up a picture of any changes.
I will then link to this table in .R to automatically produce a google visulisation of change over time per region, I have done this manually and the code looks like this
data.addColumn('string', 'Week');
data.addColumn('number', 'Australia');
data.addColumn('number', 'Canada');
data.addColumn('number', 'Caribbean');
data.addColumn('number', 'Central America');
data.addColumn('number', 'Europe');
data.addColumn('number', 'Japan');
data.addColumn('number', 'Rest of World');
data.addColumn('number', 'South America');
data.addColumn('number', 'US');
data.addRows([
['28/10/11', 42, 36, 36, 46, 23, 0, 64, 64, 4],
['04/11/11', 36, 36, 36, 46, 23, 0, 63, 64, 4],
['11/11/11', 29, 0, 36, 23, 19, 0, 63, 63, 0],
['18/11/11', 29, 0, 36, 23, 19, 0, 62, 63, 0],
['25/11/11', 29, 0, 33, 23, 18, 0, 53, 49, 0],
['02/12/11', 29, 0, 33, 23, 18, 0, 54, 49, 0],
['09/12/11', 29, 0, 33, 25, 18, 0, 54, 50, 0]
So keeping the format similar in SQL will make the automation of the graph a hell of alot easier! 😎
December 15, 2011 at 10:36 am
Ok. Read the article in my signature on how to best post sample data, then post your code with some DDL for sample data and I'll supply the pivot statement. 🙂
But ONLY if you promise to try to understand it.. *grin* .. and pass the info along if ever asked. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 15, 2011 at 10:46 am
I will get on to that!
If you check out some of my other posts I normally asks for hints and tips and an explanation of whats going on in the queries!
If I dont understand then I am never going to learn!
Will bring it all together tomorrow and post it up then.
Many Thanks
December 15, 2011 at 10:58 am
A key question here is, are the regions reasonably static? You have Australia in there. How likely is that to go away, or be split in two, or anything like that? How likely are you to add New Zealand (out of "Rest of World")? And so on.
If those are likely to remain static, there's no reason to get complex about the pivot operation. If they are likely to change, then the pivot is much more complex.
- 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
December 15, 2011 at 11:20 am
GSquared (12/15/2011)
A key question here is, are the regions reasonably static? You have Australia in there. How likely is that to go away, or be split in two, or anything like that? How likely are you to add New Zealand (out of "Rest of World")? And so on.If those are likely to remain static, there's no reason to get complex about the pivot operation. If they are likely to change, then the pivot is much more complex.
Oh, don't worry, I wasn't going down the complex road just yet. I was simply going to help with the pivot statement itself. If it's a change set of countries/values, thats a whole 'nother story. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 16, 2011 at 2:51 am
The Regions will remain static!
Just going to work on uploading the sample data...
December 16, 2011 at 3:13 am
Sample Data
CREATE TABLE Regions_Pivot_Test
(
Fail varchar(3),
Region varchar(30),
Class varchar(30),
Date varchar(10)
)
INSERT INTO Regions_Pivot_Test
(Fail, Region, Class, Date)
SELECT '42','Australia','Construction','28/10/2011' UNION ALL
SELECT '36','Canada','Construction','28/10/2011' UNION ALL
SELECT '36','Caribbean','Construction','28/10/2011' UNION ALL
SELECT '46','Central America','Construction','28/10/2011' UNION ALL
SELECT '23','Europe','Construction','28/10/2011' UNION ALL
SELECT '0','Japan','Construction','28/10/2011' UNION ALL
SELECT '64','Rest of World','Construction','28/10/2011' UNION ALL
SELECT '64','South America','Construction','28/10/2011'UNION ALL
SELECT '4','US','Construction','28/10/2011'UNION ALL
SELECT '36','Australia','Construction','04/11/2011' UNION ALL
SELECT '36','Canada','Construction','04/11/2011' UNION ALL
SELECT '36','Caribbean','Construction','04/11/2011' UNION ALL
SELECT '46','Central America','Construction','04/11/2011' UNION ALL
SELECT '23','Europe','Construction','04/11/2011' UNION ALL
SELECT '0','Japan','Construction','04/11/2011' UNION ALL
SELECT '63','Rest of World','Construction','04/11/2011' UNION ALL
SELECT '64','South America','Construction','04/11/2011'UNION ALL
SELECT '4','US','Construction','04/11/2011'
Pretty sure that should do the trick! Thanks again.....
December 16, 2011 at 6:42 am
A few notes:
This assumes the [Fail] value is what you want pivoted.
That the value is not to be summed, averaged per [Date] [Region] group.
That their is only one value for [Fail] per [Date] [Region] group. If not, then more info is needed.
That the [Region] values are static (as you stated above).
DECLARE @Regions_Pivot_Test TABLE
(Fail VARCHAR(3)
,Region VARCHAR(30)
,Class VARCHAR(30)
,Date VARCHAR(10)
)
INSERT INTO @Regions_Pivot_Test (Fail, Region, Class, [Date])
SELECT '42','Australia','Construction','28/10/2011' UNION ALL
SELECT '36','Canada','Construction','28/10/2011' UNION ALL
SELECT '36','Caribbean','Construction','28/10/2011' UNION ALL
SELECT '46','Central America','Construction','28/10/2011' UNION ALL
SELECT '23','Europe','Construction','28/10/2011' UNION ALL
SELECT '0','Japan','Construction','28/10/2011' UNION ALL
SELECT '64','Rest of World','Construction','28/10/2011' UNION ALL
SELECT '64','South America','Construction','28/10/2011'UNION ALL
SELECT '4','US','Construction','28/10/2011'UNION ALL
SELECT '36','Australia','Construction','04/11/2011' UNION ALL
SELECT '36','Canada','Construction','04/11/2011' UNION ALL
SELECT '36','Caribbean','Construction','04/11/2011' UNION ALL
SELECT '46','Central America','Construction','04/11/2011' UNION ALL
SELECT '23','Europe','Construction','04/11/2011' UNION ALL
SELECT '0','Japan','Construction','04/11/2011' UNION ALL
SELECT '63','Rest of World','Construction','04/11/2011' UNION ALL
SELECT '64','South America','Construction','04/11/2011'UNION ALL
SELECT '4','US','Construction','04/11/2011'
SELECT
Class
,[Date]
--- the items/categories being pivoted on
,[Australia],[Canada],[Caribbean],[Central America]
,[Europe],[Japan],[Rest of World],[South America],[US]
FROM
(SELECT
Fail
,Region
,Class
,[Date]
FROM
@Regions_Pivot_Test) AS qry --- the source data to pivot
PIVOT
(MAX(Fail) --- using MAX here because you're not calculating anything, just pivoting
FOR Region IN ([Australia],[Canada],[Caribbean],[Central America]
,[Europe],[Japan],[Rest of World],[South America],[US])
) AS pvt
Hope this helps and gives you something to chew on. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply