CODEWITHSUNDEEP
sqlt-sqlpivotpivot-table
dp3
dp3

Reputation: 1677

Creating a pivot table in T-SQL?

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.

Upvotes: 1

Views: 238

Answers (1)

Taryn
Taryn

Reputation: 247850

You should be able to join on the table multiple times to get the result that you need:

select t1.region,
  t1.location,
  t1.customer,
  t1.customerkey,
  t1.status,
  case when t2.status is not null then 'Yes' else 'No' end VIPStatus
from yourtable t1
left join yourtable t2
  on t1.CustomerKey = t2.CustomerKey 
  and t2.Location = 'All States' 
  and t2.status = 'VIP Customer'
where t1.Location <> 'All States'

See SQL Fiddle with Demo

The result is:

| 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 |       Yes |
|   West | Washington |     Greg |         114 | Inactive |       Yes |
|   West |       Utah |      Tim |         115 |   Active |        No |

Upvotes: 3

Related Questions