October 31, 2012 at 10:37 am
Hi Guys,
I am a bit stuck trying to pivot some data in SQL and was wondering if you could offer some guidance?!
My data looks like this:
create table TestData(Description varchar(255), Region_Code varchar(10), WholeACNet float)
insert into TestData values ('Original', 'AU', 1973026688)
insert into TestData values ('RiverLakes_WWFL', 'AU', 112926275)
insert into TestData values ('RiverLakesSev123_WWFL', 'AU', 1718559478)
insert into TestData values ('Original', 'BR', 1000539676)
insert into TestData values ('RiverLakes_WWFL', 'BR', 445413235)
insert into TestData values ('RiverLakesSev123_WWFL', 'BR', 1059705496)
insert into TestData values ('Original', 'CA', 2269091781)
insert into TestData values ('RiverLakes_WWFL', 'CA', 890979921)
insert into TestData values ('RiverLakesSev123_WWFL', 'CA', 997114545)
I would like to pivot it so the headers read: Region_Code, Original, RiverLakes_WWFL and RiverLakesSev123_WWFL
Any help would be much appreciated! 😀
October 31, 2012 at 11:33 am
Would this work for you ?
SELECT Original = CASE WHEN description = 'Original' THEN Region_Code ELSE '' END,
RiverLakes_WWFL = CASE WHEN description = 'RiverLakes_WWFL' then Region_Code ELSE '' END,
RiverLakesSev123_WWFL = CASE WHEN description = 'RiverLakesSev123_WWFL' THEN Region_Code ELSE '' END,
WholeACNet FROM [TestAndLearn].[dbo].[TestData]
October 31, 2012 at 11:38 am
I am new here. I need your advise.
In my previous reply I designed queryin my SQL Server and copy paste here. It is looking flat text in my reply. I saw your post is in window with text
color and has better readability. Could you tell me how did you post such a way that is much better than mine.
Thank you
October 31, 2012 at 11:39 am
Thanks for the reply....
Hmmm not quite what I was after.
This places the Region Code under the Original, RiverLaKes_WWFL and RiverLakesSev123_WWFL and gives the WholeACNet in one column.
Ideally Region_Code would be one column and the values for the 3 types to the right of that......
Thanks again! 🙂
October 31, 2012 at 11:41 am
T_Dot_Geek (10/31/2012)
I am new here. I need your advise.In my previous reply I designed queryin my SQL Server and copy paste here. It is looking flat text in my reply. I saw your post is in window with text
color and has better readability. Could you tell me how did you post such a way that is much better than mine.
Thank you
When you compose a message on the left hand side there are some tags. Just wrap those around the code
[ c o d e = " o t h e r " ] *code goes here* [ / c o d e ] without the spaces!
October 31, 2012 at 11:44 am
Thanks. Could you please create a sample in excel including your columns values and position and post here for better understanding.
October 31, 2012 at 11:56 am
SELECT Region_Code, Original = COUNT(CASE WHEN description = 'Original' THEN 1 ELSE 0 END),
RiverLakes_WWFL = COUNT(CASE WHEN description = 'RiverLakes_WWFL' then 1 ELSE 0 END),
RiverLakesSev123_WWFL = count(CASE WHEN description = 'RiverLakesSev123_WWFL' THEN 1 ELSE 0 END)
FROM [TestAndLearn].[dbo].[TestData]
group by region_code
October 31, 2012 at 12:04 pm
If your goal is to create a crosstab result, then by far the easiest way to do it is with a matrix report in Reporting Services. Did you need to have it returned as a SQL query result?
October 31, 2012 at 12:23 pm
Hi
Does this do what you want? I've used sum to aggregate the WholeACNet.
SELECT Region_Code,
[Original],
[RiverLakes_WWFL],
[RiverLakesSev123_WWFL]
FROM TestData
PIVOT (
SUM(WholeACNet)
FOR Description in (
[Original],
[RiverLakes_WWFL],
[RiverLakesSev123_WWFL]
)
) p
October 31, 2012 at 12:35 pm
Sorry for the previous post.Added count was not right. Here is a solution using CASE Statement.
SELECT Region_Code, Original = Max(CASE WHEN description = 'Original' THEN WholeACNet END),
RiverLakes_WWFL = max(CASE WHEN description = 'RiverLakes_WWFL' then WholeACNet END),
RiverLakesSev123_WWFL = Max(CASE WHEN description = 'RiverLakesSev123_WWFL' THEN WholeACNet END)
FROM [TestAndLearn].[dbo].[TestData]
group by region_code
November 1, 2012 at 3:37 am
micky,
This works perfect!
Thankyou for taking the time to help me out 😀
November 1, 2012 at 3:38 am
T_Dot_Geek
This solution also works as well!
Thanks again for looking into this for me!.
Cheeeeers 😎
November 1, 2012 at 8:30 am
Thank you Micky
You have defined very simply using PIVOT command. I understood first time how to use PIVOT very well from your code.
I can see that PIVOT reduce the code but part from that what are the other advantages using PIVOT over CASE Statement.
Would PIVOT execute faster than CASE ?
November 1, 2012 at 9:54 am
Be careful methexis, because both queries aren't the same. One uses MAX and the other uses SUM. In this case the show the same results but it won't be like this everytime.
For a comparative on Cross Tabs against PIVOT, you can read the articles by Jeff Moden.
November 1, 2012 at 12:53 pm
Luis Cazares (11/1/2012)
Be careful methexis, because both queries aren't the same. One uses MAX and the other uses SUM. In this case the show the same results but it won't be like this everytime.For a comparative on Cross Tabs against PIVOT, you can read the articles by Jeff Moden.
Definitely be careful to choose the proper method to aggregate the data ... in the sample data you provided there wasn't any issues with aggregation, so I randomly picked an aggregation method as you hadn't specified how you wanted the values handled.
Jeff's articles provide really good information on the different methods and their performance.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply