SQL Tip: flattening data results into columns

Flattening (sometimes called pivoting or denormalising) is a way to reshape data so that categories turn into columns. The benefit is immediate: queries run faster with fewer joins, data reads more clearly for both humans and BI tools, aggregations across columns become trivial, and analytics pipelines can plug directly into “wide” tables without extra prep.

We'll walk through two practical approaches to flattening in SQL Server: one using XML extraction for flexibility, and one using PIVOT for cleaner, maintainable results.

The data

Here’s a sample snapshot of follower counts by industry for Cortex:

OrganisationName	IndustryDescription	OrganicFollowerCount	arc_start_date	arc_end_date
Cortex Technologies	Management Consulting	17	2024-06-12	2024-06-13
Cortex Technologies	Computer Software	21	2024-06-12	2024-06-13
Cortex Technologies	Banking	10	2024-06-12	2024-06-13
Cortex Technologies	Financial Services	20	2024-06-12	2024-06-13
Cortex Technologies	Information Technology & Services	36	2024-06-12	2024-06-13

We want to flatten this by month-end dates so each industry is a column and each row is a snapshot in time.

Leveraging Bragi’s `dim_date` Table

Bragi includes a handy dim_date table with all dates from 1900 to 2999, including a LastDayOfMonth column that's perfect for monthly aggregations:

SELECT DISTINCT LastDayOfMonth
FROM dbo.dim_date
WHERE yyyy = 2024;

This gives you all month-ends for the year, which is what you'll need to join against.

Approach 1: XML Pivot (when you know your categories)

This query creates XML fragments for each industry sector relevant to a company on each month end. It’s a neat way to aggregate when you want flexibility or semi-structured output:

SELECT
    dd.LastDayOfMonth,
    (
        SELECT
            li.IndustryDescription AS [@IndustryDescription],
    li.OrganicFollowerCount AS [@OrganicFollowerCount]
FROM dbo.arc_LinkedIn_10_LifeTimeFollowersByIndustry li
WHERE dd.LastDayOfMonth BETWEEN li.arc_start_date AND li.arc_end_date
  AND IndustryDescription IN ('Information Technology & Services', 'Financial Services', 'Computer Software', 'Management Consulting', 'Banking')
  AND OrganisationName = 'Cortex Technologies'
    FOR XML PATH('Industry'), TYPE
    ) AS IndustryData
FROM (
    SELECT DISTINCT LastDayOfMonth
    FROM dbo.dim_date
    WHERE yyyy = 2024 AND LastDayOfMonth < GETDATE()
    ) dd;

Sample IndustryData output:

<Industry IndustryDescription="Banking" OrganicFollowerCount="11"/> <Industry IndustryDescription="Information Technology & Services" OrganicFollowerCount="37"/> <Industry IndustryDescription="Financial Services" OrganicFollowerCount="21"/> <Industry IndustryDescription="Management Consulting" OrganicFollowerCount="16"/> <Industry IndustryDescription="Computer Software" OrganicFollowerCount="22"/>

Extracting XML data into columns

You can then extract data into named columns like this:

WITH XMLData AS (
    SELECT
        dd.LastDayOfMonth,
        (
            SELECT
                li.IndustryDescription AS [@IndustryDescription],
    li.OrganicFollowerCount AS [@OrganicFollowerCount]
FROM dbo.arc_LinkedIn_10_LifeTimeFollowersByIndustry li
WHERE dd.LastDayOfMonth BETWEEN li.arc_start_date AND li.arc_end_date
  AND IndustryDescription IN ('Information Technology & Services', 'Financial Services', 'Computer Software', 'Management Consulting', 'Banking')
  AND OrganisationName = 'Cortex Technologies'
    FOR XML PATH('Industry'), TYPE
    ) AS IndustryData
FROM (
    SELECT DISTINCT LastDayOfMonth
    FROM dbo.dim_date
    WHERE yyyy = 2024 AND LastDayOfMonth < GETDATE()
    ) dd
    )
SELECT
    LastDayOfMonth,
    IndustryData.value('(/Industry[@IndustryDescription="Information Technology &amp; Services"]/@OrganicFollowerCount)', 'INT') AS [Information Technology & Services],[1]
IndustryData.value('(/Industry[@IndustryDescription="Financial Services"]/@OrganicFollowerCount)', 'INT') AS [Financial Services],[1]
IndustryData.value('(/Industry[@IndustryDescription="Computer Software"]/@OrganicFollowerCount)', 'INT') AS [Computer Software],[1]
IndustryData.value('(/Industry[@IndustryDescription="Management Consulting"]/@OrganicFollowerCount)', 'INT') AS [Management Consulting],[1]
IndustryData.value('(/Industry[@IndustryDescription="Banking"]/@OrganicFollowerCount)', 'INT') AS [Banking][1]
FROM XMLData;

Heads up: This method requires you to manually add each industry column and can quietly fail if an industry name changes unexpectedly.

Approach 2: SQL Server PIVOT (easier to maintain)

PIVOT lets you rotate rows into columns with less hassle and better performance. It works especially well for reports that have categorical data like industry sectors:

SELECT LastDayOfMonth,
    [Banking],
    [Computer Software],
    [Financial Services],
    [Information Technology & Services],
    [Management Consulting]
FROM (
    SELECT dd.LastDayOfMonth,
    li.IndustryDescription,
    li.OrganicFollowerCount
    FROM dbo.arc_LinkedIn_10_LifeTimeFollowersByIndustry li
    INNER JOIN (SELECT DISTINCT LastDayOfMonth
    FROM dbo.dim_date
    WHERE yyyy = 2024) dd
    ON dd.LastDayOfMonth BETWEEN li.arc_start_date AND li.arc_end_date
    ) src
    PIVOT (
    MAX(OrganicFollowerCount)
    FOR IndustryDescription IN ([Banking], [Computer Software], [Financial Services], [Information Technology & Services], [Management Consulting])
    ) pvt
ORDER BY pvt.LastDayOfMonth;
Dynamic PIVOT Columns

Keep your PIVOT columns up to date automatically with STRING_AGG:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(IndustryDescription), ',')
FROM (SELECT DISTINCT IndustryDescription
      FROM dbo.arc_LinkedIn_10_LifeTimeFollowersByIndustry) AS industries;

SET @query = '
SELECT LastDayOfMonth, ' + @cols + '
FROM (
SELECT dd.LastDayOfMonth,
li.IndustryDescription,
li.OrganicFollowerCount
FROM dbo.arc_LinkedIn_10_LifeTimeFollowersByIndustry li
INNER JOIN (SELECT DISTINCT LastDayOfMonth
FROM dbo.dim_date
WHERE yyyy = 2024) dd
ON dd.LastDayOfMonth BETWEEN li.arc_start_date AND li.arc_end_date
) src
PIVOT (
MAX(OrganicFollowerCount)
FOR IndustryDescription IN (' + @cols + ')
) pvt
ORDER BY pvt.LastDayOfMonth;';

EXEC sp_executesql @query;

This way you're not manually maintaining an ever-changing column list.

Summary

Wide, flat tables aren’t always the right model, but when you’re reporting, building dashboards, or prepping data for analytics, they make life much easier. You’ve seen two practical ways to get there:

XML + value extraction: flexible but manual.

PIVOT / dynamic PIVOT: cleaner, faster, and easier to maintain.

Which you use depends on how predictable your categories are and how much control you need. Either way, flattening gives you tables that query faster, read clearer, and drop straight into BI or ML pipelines without extra wrangling.

09 March 2026