0

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
Vu Le
  • 73
  • 1
  • 9
  • 1
    how did you get 3 on col1 ? please provide your logic too – Avi Jan 24 '19 at 06:33
  • @Avi, sorry that was a typo. I am not sure what you mean by logic but I have provided code on how I am Unpivoting the table – Vu Le Jan 24 '19 at 06:44
  • try reading this [link](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) it might help you. :) – Buchiman Jan 24 '19 at 06:51

4 Answers4

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.

Rex
  • 2,130
  • 11
  • 12
0

You can try below - using row_number()

SELECT a.col1, a.[all],row_number() over(partition by a.col1 order by a.[all]) as position
from df1 as t
UNPIVOT
(
[all] for col_nm in (
    col2, col3, col4
) as a
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • this does not seem to work - when using OrderBy the row_number() might not give you the correct column index... – Rex Jan 24 '19 at 07:00
  • @Rex, you better give it a try and show me that it won't work – Fahmi Jan 24 '19 at 07:06
  • ok, i did a try now, and confirmed it does not work - you can try your query after tweaking the first value "AAA" to be "ZZZ", you will see the column number becomes 3, because the order-by made it the last! – Rex Jan 24 '19 at 07:08
0

Try this 

select * from (

select col1,1 as position , col2  as [All] from [Table] 
Union
select col1,2 as position, col3  as [All] from [Table] 
Union
select col1,3 as position, col4  as [All] from [Table] )
where [all] is not null;
Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
0

You can use APPLY :

SELECT df1.col1, dff.*     
FROM df1 as df CROSS APPLY
     ( VALUES (col2, 1), (col3, 2), (col4, 3) ) dff([All], [Position])
WHERE dff.[All] IS NOT NULL;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52