Rename SQL query column names dynamically

Question

I have two tables: data and dataMAP. The data table has column names MapID, Real_0, Real_2, Real_3, up to Real_19. Sample data:

1,1.1,2.1,3.1,4.1
1,1.2,2.2,3.2,4.2
1,1.3,2.3,3.3,4.3

DataMAP has has MapID, Real_0_Name, Real_1_Name, to to Real_19_Name. sample data:

1,'forceW','forceX','forceY','forceZ'
2,'distanceW','distanceX','distanceY','distanceZ'

When I query the data table for a given dataMAP ID I'd like the resulting column names to use the values stored in dataMAP for that mapID number.

select * from data where datamap=1 results in:

mapid,Real_0, Real_1, Real_3, Real_4 (these are the column names)

1,1.1,2.1,3.1,4.1

1,1.2,2.2,3.2,4.2

1,1.3,2.3,3.3,4.3

But I want:

MapID,forceW,forceX,forceY,forceZ (these are the column names)

1,1.1,2.1,3.1,4.1

1,1.2,2.2,3.2,4.2

1,1.3,2.3,3.3,4.3

The server is Microsoft SQL 2012.

Why do you want to this in T-SQL instead of the client application? — Dan Guzman
– Dan Guzman, Commented Mar 10, 2018 at 20:56
The client application (probably an Excel query) is outside of my control and will be used by someone with no SQL / programming experience. — JPoole
– JPoole, Commented Mar 10, 2018 at 21:04

Aleksei Lychev · Accepted Answer · 2018-03-11 07:05:07Z

1

I couldn't figure it out without dynamic SQL:

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

IF OBJECT_ID('tempdb..#values') IS NOT NULL
    DROP TABLE #values

SELECT
    vMapID, N, dm.ColName, [values].[value]
INTO #values
FROM (
    SELECT
        MapID AS vMapID,
        N,
        cn.ColName + '_Name' AS RealName,
        cn.value
    FROM
    (
        SELECT
            d.MapID,
            d.Real_0, d.Real_1, d.Real_2, d.Real_3,
            ROW_NUMBER() OVER(PARTITION BY d.MapID ORDER BY d.MapID, Real_0) AS N
        FROM [data] d
    ) AS piv
    UNPIVOT
    (
        [value] FOR ColName IN (Real_0, Real_1, Real_2, Real_3)
    ) AS cn
) [values]
INNER JOIN (
    SELECT
        MapID AS mMapID,
        cn.RealName,
        cn.ColName
    FROM DataMAP AS dm
    UNPIVOT
    (
        ColName FOR RealName IN (Real_0_Name, Real_1_Name, Real_2_Name, Real_3_Name)
    ) cn
) dm
    ON dm.mMapID = [values].vMapID
    AND dm.RealName = [values].RealName
WHERE [values].vMapID = 2

SELECT @cols = STUFF(
        (SELECT DISTINCT ',' + QUOTENAME(ColName) FROM #values FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 1, '')

SET @query = 'SELECT MapID, ' + @cols + ' FROM 
             (
                SELECT
                    vMapID AS MapID,
                    N,
                    ColName,
                    [value]
                FROM #values
            ) v
            PIVOT
            (
                MIN([value])
                FOR ColName IN (' + @cols + ')
            ) p '

EXEC(@query)

Both tables are UNPIVOT-ed to get actual numbers as Name+Value pair, ROW_NUMBER() is added to distinguish one row from another.

answered Mar 11, 2018 at 7:05

Aleksei Lychev

1511 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JPoole Over a year ago

Cool, I think that it works! But, I over simplified my requirement a little. In addition to the real values real_0, real_1, etc. I also have 5 string values string_0, string_1, etc. I added them but get this error: "The type of column "string_0" conflicts with the type of other columns specified in the UNPIVOT list."

JPoole Over a year ago

I've thought of another method. Query the data into a temp table, then rename the columns using sp_rename. This requires a cursor to loop through the table of new column names but seems to be 4x faster on my sample data set of 16k records. I guess that a cursor looping though a table of 20 column names is a lot faster than unpivoting and then pivoting the full data set? Is there any reason not to use sp_rename? If not I'll post by code.

Collectives™ on Stack Overflow

Rename SQL query column names dynamically

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related