SQL Pivot on string value instead of numeric

Question

I have an existing table with the following data:

InputName   ResolvedName    Type    InsertDate
Server_1    Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM
Server_2    Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM
Server_3    Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM
Server_4    Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM
Server_5    Cluster_Alias_1 ABC 9/18/2018 1:55:12 PM
Server_6    Cluster_Alias_1 ABC 9/18/2018 1:55:12 PM
Server_7    Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM
Server_8    Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM
Server_9    Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM
Server_10   Cluster_Alias_1 ABC 9/13/2018 7:47:19 PM

And I am trying to use SQL Pivot to have it look like this:

ResolvedName    InputName1  InputName2  InputName3  InputName4  InputName5  InputName6  InputName7  InputName8  InputName9  InputName10
Cluster_Alias_1 Server_1    Server_2    Server_3    Server_4    Server_5    Server_6    Server_7    Server_8    Server_9    Server_10

I have looked at multiple solutions and everything involves pivoting on a number, but everything is referring to a number instead of a string.

Links I have tried:

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

Convert Rows to columns using 'Pivot' in SQL Server

https://data.stackexchange.com/stackoverflow/query/497432

score 3 · Accepted Answer · answered Jul 23 '19 at 18:03

Unless you need to do this dynamically based on the number of Inputs, you need to just create the column names using row_number, and pivot based on the combined name of 'InputName' + row_number

Something like:

select * 
from (
    select ResolvedName, 
           InputName, 
           concat('InputName',row_number() over (partition by ResolvedName order by InputName)) ColumnNames
    from [table]
) t 
pivot (
    max(InputName)
    for ColumnNames in ([InputName1],[InputName2],[InputName3],[InputName4],[InputName5],[InputName6],[InputName7],[InputName8])
) p

you may also use `dynamic sql` to make it more flexible. Right now it is bound to 10 server. — DarkRob, Jul 23 '19 at 18:14

score 2 · Answer 2 · answered Jul 23 '19 at 17:57

Not entirely sure, but I guess you are looking for something like below-

SELECT ResolvedName,
MAX(CASE WHEN InputName = 'Server_1' THEN InputName ELSE NULL END)  InputName1, 
MAX(CASE WHEN InputName = 'Server_2' THEN InputName ELSE NULL END)  InputName2, 
MAX(CASE WHEN InputName = 'Server_3' THEN InputName ELSE NULL END)  InputName3, 
MAX(CASE WHEN InputName = 'Server_4' THEN InputName ELSE NULL END)  InputName4, 
MAX(CASE WHEN InputName = 'Server_5' THEN InputName ELSE NULL END)  InputName5, 
MAX(CASE WHEN InputName = 'Server_6' THEN InputName ELSE NULL END)  InputName6, 
MAX(CASE WHEN InputName = 'Server_7' THEN InputName ELSE NULL END)  InputName7, 
MAX(CASE WHEN InputName = 'Server_8' THEN InputName ELSE NULL END)  InputName8, 
MAX(CASE WHEN InputName = 'Server_9' THEN InputName ELSE NULL END)  InputName9, 
MAX(CASE WHEN InputName = 'Server_10' THEN InputName ELSE NULL END)  InputName10
FROM your_table
GROUP BY ResolvedName

score 2 · Answer 3 · answered Jul 23 '19 at 18:21

You may try this for dynamic query.

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

    select @cols = STUFF((SELECT ',' + concat('InputName',row_number() over (partition by ResolvedName order by InputName)) 
                        from [table]
                        group by ResolvedName    
                        order by ResolvedName    
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')


    set @query = 'select * 
    from (
        select ResolvedName, 
               InputName, 
               concat(''InputName'',row_number() over (partition by ResolvedName order by InputName)) ColumnNames
        from [table]
    ) t 
    pivot (
        max(InputName)
        for ColumnNames in (' + @cols + ')
    ) p'

    execute(@query);

SQL Pivot on string value instead of numeric

3 Answers3