asked
Creating a pivot table?
Hey everyone, I could really use some help creating a pivot table. I have data in some rows that instead need to be appear in columns, juxtaposed next to values in other records. The data is currently in the following format: Region | Location | Customer | CustomerKey |Status North | New York | John | 111 |Active North | New York | Mary | 112 |Active North | Delaware | Bob | 113 |Idle North | New Jersey| Bob | 113 |Active West | California| Bob | 113 |Inactive West | Washington| Greg | 114 |Inactive West | Utah | Tim | 115 |Active North | All States | Bob | 113 |VIP Customer North | All States | Mary | 112 |Regular Customer West | All States | Bob | 113 |Regular Customer West | All States | Tim | 115 |Regular Customer West | All States | Greg | 114 |VIP Customer North | All States | John | 111 |Regular Customer The issue is with the 'Status' column, which can have one group of values (Inactive/Active/Idle) and another (VIP Customer and Regular Customer). When the 'Location' column is 'All States', it uses the VIP/Regular values. I would like to add a column, to have the data appear along the lines of: Region | Location | Customer | CustomerKey |Status | VIPStatus North | New York | John | 111 |Active | No North | New York | Mary | 112 |Active | No North | Delaware | Bob | 113 |Idle | Yes North | New Jersey| Bob | 113 |Active | Yes West | California| Bob | 113 |Inactive | No West | Washington| Greg | 114 |Inactive | Yes West | Utah | Tim | 115 |Active | No Basically, if the Customer has a record with the Status of a 'VIP Customer', under a combination of a Region and a corresponding Location value of 'All States', then it will show a 'VIPStatus' of 'Yes' or 'No' under any record of that customer under that given Region (regardless of the Location state). Is there a simple solution for this? Any help on rearranging this data would in T-SQL would be greatly appreciated.
As long as there is only one row per customer for a location value of 'All States', then you can use a join onto a derived view of the base table.... select Region, Location, Customer, CustomerKey, Status, case when T2.status = 'VIP Customer' then 'Yes' when T2.status = 'Regular Customer' then 'No' end as VIPStatus from YourTable T1 join (select CustomerKey, Status from YourTable where location = 'All States' ) T2 on T1.CustomerKey = T2.CustomerKey where location 'All States'
Thank you, I tweaked this just a bit and it worked. ...CASE WHEN T2.Status IS NOT NULL THEN 'Yes' ELSE 'No' END VIPStatus FROM Table T1 LEFT JOIN Table T2 ON T1.CustomerKey = T2.CustomerKey AND T1.Region = T2.Region AND T2.Location = 'All States' AND T2.Status = 'VIP Customer' WHERE T1.Location <> 'All States' Worked like a charm, thank you.
11 People are following this question.
