I worked many years with the NPI dataset. Transforming the data brought actionable insight.
Imagine a conversation between an enterprise IT strategist, a healthcare IT consultant, and a healthcare administrator. The three sit around a conference table reviewing a dataset pulled from the National Plan and Provider Enumeration System (NPPES), full of NPIs, taxonomy codes, and—what’s this?—taxonomy codes spread across multiple columns.
Act I: The Dataset on the Table
The administrator begins:
“We need to analyze our provider network—how many cardiologists we have, who’s practicing under what license. But our system’s export has Taxonomy1, Taxonomy2, Taxonomy3… up to 15 in some cases.”
The strategist raises an eyebrow.
“Ah, the wide format. Hard to aggregate. Hard to join. A dashboard killer.”
The healthcare IT consultant, already opening SQL Server Management Studio, grins.
“Time to unpivot.”
Act II: Why Unpivot?
Let’s step back.
In healthcare, providers can have multiple specialties or roles—what CMS captures as taxonomy codes. When these codes are spread across multiple columns (Taxonomy1, Taxonomy2, etc.), it’s great for forms. Not so much for analysis.
Unpivoting transforms those multiple columns into multiple rows:
SELECT NPI_NPI, Taxonomy, TaxonomyLevel
FROM (
SELECT NPI_NPI, Taxonomy1, Taxonomy2, Taxonomy3
FROM ProviderData
) p
UNPIVOT (
Taxonomy FOR TaxonomyLevel IN (Taxonomy1, Taxonomy2, Taxonomy3)
) AS up;
Now, NPI_NPI is preserved, Taxonomy holds the specialty code, and TaxonomyLevel tells you whether it came from column 1, 2, or 3.
What was once:
| NPI_NPI | Taxonomy1 | Taxonomy2 | Taxonomy3 |
|---|---|---|---|
| 123456 | CodeA | CodeB | CodeC |
Becomes:
| NPI_NPI | Taxonomy | TaxonomyLevel |
|---|---|---|
| 123456 | CodeA | Taxonomy1 |
| 123456 | CodeB | Taxonomy2 |
| 123456 | CodeC | Taxonomy3 |
Act III: But Who Should Do This Work?
At this point in the meeting, the administrator asks the question behind the question:
“Who’s supposed to do this kind of work? My credentialing team? Our compliance lead? Or IT?”
The strategist leans forward.
“Depends on the use case. But here’s who typically wields the SQL scalpel on provider data…”
| Role | Why They Use SQL on Taxonomy Data |
|---|---|
| Data Analyst | For dashboards, specialty counts, productivity stats |
| MDM Specialist | To deduplicate, cleanse, and merge provider records |
| FWA Analyst | To flag anomalies between procedures and specialty |
| Data Engineer | For transforming and piping normalized data |
| HIE Integration Analyst | To harmonize taxonomies across systems |
| Compliance / Credentialing | To verify provider roles match license scope |
| Clinical Informatics | To evaluate care delivery by specialty |
| Pop Health Analyst | For attribution and network adequacy modeling |
Unpivoting is just one move in their toolkit. But it’s foundational. Without normalized taxonomy data, downstream efforts break down.
Act IV: Real-World Scenarios
The consultant clicks into a Jupyter notebook:
- Compliance Check: “Let’s compare provider specialties against state licensure databases. Anything not aligned gets flagged.”
- Referral Network Analysis: “We can now group providers by taxonomy and region to spot gaps—are there enough dermatologists in El Paso?”
- Machine Learning Prep: “Our unpivoted data lets us one-hot encode specialties to predict telehealth adoption.”
The administrator smiles.
“This makes our provider network visible—clean, flexible, and ready for insight.”
Final Thoughts: A Hidden Lever of Healthcare IT
This isn’t just about SQL syntax or data reshaping.
It’s about seeing. Unpivoting provider taxonomies opens up visibility into provider roles, compliance risks, network coverage, and clinical trends. But it also brings up deeper questions for any health system:
- Are the right roles doing the right work with the data?
- Do your teams understand the shape of the data before it gets reshaped?
- Is the business using data as a strategic asset or a bureaucratic burden?
When these roles—analyst, engineer, strategist, administrator—come together, transformation happens. And often, it starts with a little SQL and a well-timed unpivot.
