get the positon of a column where value exists while unpivoting the table in SQL

Question

I am trying to get the column position of a value while doing an unpivot on the columns col2, col3, col4 in SQL server

df1
col1     col2     col3     col4     
1        AAA      BBB
2        ABC      CCC      DDD

result
col1     [All]     [position]
1        AAA        1
1        BBB        2
2        ABC        1
2        CCC        2
2        DDD        3

I am able to unpivot the table by using the following

SELECT a.col1, a.[all]
from df1 as t
UNPIVOT
(
[all] for col_nm in (
    col2, col3, col4
) as a

Answer 1

if you just need to know which column it's from, i think you can simply include the col_nm into your select statement:

SELECT a.col1
      ,a.[all]
      ,a.col_nm
FROM   df1 AS t UNPIVOT([all] FOR col_nm IN(col2, col3, col4)) AS a;

if you need to know the column index number - then it should be easy based on above - perhaps do a switch-case check for the column name; or,

if this is a physical or temp table, you can join back with sys.tables and sys.columns to find the column index (columnId) based on the column name.