CTE Column Names: Rename Positions Without Moving Values

CTE column names can be supplied outside the query that produces their values. I check that list by position before treating its labels as a report contract.

Start with the names produced inside the CTE

A dispatch extract needs a shipment identifier and a destination. The sample supplies three rows, including one shipment whose destination is missing. Both source expressions have explicit types, so the example keeps naming separate from type inference.

Without an external column list, the aliases inside Dispatch become the names used by the consuming SELECT. The first result has InnerId and InnerPlace. Their names come from the expressions after AS, while their values come from the supplied rows.

WITH Dispatch AS
(
    SELECT CAST(v.Id AS int) AS InnerId,
           CAST(v.Place AS nvarchar(20)) AS InnerPlace
    FROM (VALUES (101,N'Pune'),(102,N'Surat'),(103,NULL)) AS v(Id,Place)
)
SELECT InnerId,InnerPlace FROM Dispatch ORDER BY InnerId;

The first two destinations are Pune and Surat. The third stays NULL beside identifier 103. Renaming a column should not turn that missing value into empty text or remove its shipment.

Expose names for the consuming statement

The second statement adds ShipmentId and Destination immediately after the CTE name. This list supplies the CTE column names. The outer SELECT now refers to those names even though the inner expressions still use InnerId and InnerPlace.

WITH Dispatch (ShipmentId,Destination) AS
(
    SELECT CAST(v.Id AS int) AS InnerId,
           CAST(v.Place AS nvarchar(20)) AS InnerPlace
    FROM (VALUES (101,N'Pune'),(102,N'Surat'),(103,NULL)) AS v(Id,Place)
)
SELECT ShipmentId,Destination FROM Dispatch ORDER BY ShipmentId;

ShipmentId receives the first expression and Destination receives the second. The same three tuples remain in the same explicit display order. The list changes the exposed identifiers, rather than moving or converting the values.

I find an explicit list useful when a report needs stable names while its internal expression names remain technical. It also makes that boundary easy to inspect. The list must match the number of produced columns, and its names must be unique within the CTE.

The outer statement uses the names exposed by Dispatch. An inner alias does not provide an alternative CTE column name after the explicit list replaces it. Keep the two naming levels clear when tracing a reference in a longer query.

A reversed list does not swap the data

The third statement intentionally names the identifier Destination and the place ShipmentId. This is a naming mistake shown as a diagnostic example. It demonstrates why a plausible-looking list needs to be checked against each expression position.

WITH Dispatch (Destination,ShipmentId) AS
(
    SELECT CAST(v.Id AS int) AS InnerId,
           CAST(v.Place AS nvarchar(20)) AS InnerPlace
    FROM (VALUES (101,N'Pune'),(102,N'Surat'),(103,NULL)) AS v(Id,Place)
)
SELECT Destination,ShipmentId FROM Dispatch ORDER BY Destination;

Destination contains the integers 101, 102 and 103. ShipmentId contains Pune, Surat and NULL. The expressions have not exchanged positions, and the integer output has not become text merely because its new name suggests a location.

Renaming and reordering are separate edits. To put destination text first, change the SELECT expression order and the corresponding name list. Do not rely on matching words to pair external names with inner aliases.

Keep the report contract explicit

For a production extract, I would review name, position and SQL type together. A client mapping fields by ordinal can be affected by reordered output. A client mapping by name can be affected by renamed output. Neither contract is established by a familiar-looking grid.

Avoid SELECT star when sharing an extract with a fixed output contract. List the fields deliberately in the consuming statement. That makes a later name or position change visible and prevents an extra source expression from quietly changing the report layout.

ORDER BY belongs to the final result requirement. The name list does not guarantee row order, and a CTE does not promise independently stored results. This example makes no claim about materialization, caching or query speed.

The statements only read inline values. Each CTE belongs to the single statement that follows it, so each example repeats its definition. No persistent object or connection setting is changed.

Look at the header and the value together, and the labels stay honest.

A CTE column list is not a reorder, it is a rename by position.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.