Dynamically selecting the column to select from the row itself in SQL-CodePudding

I have a SQL Server 2018 table with some data as follows. The number of P columns are fixed but there will be too many columns. There will be multiple columns in the fashion like S1, S2 etc

Id	SelectedP	P1	P2	P3	P4	P5
1	P2	3	8	4	15	7
2	P1	0	2	6	0	3
3	P3	1	15	2	1	11
4	P4	3	4	6	2	4

I need to write a SQL statement which can get the below result. Basically which column that needs to be selected from each row depends upon the SelectedP value in that row itself. The SelectedP contains the column to select for each row.

Id	SelectedP	Selected-P-Value
1	P2	8
2	P1	0
3	P3	2
4	P4	2

Thanks in advance.

CodePudding user response：

You just need a CASE expression...

SELECT
  id,
  SelectedP,
  CASE SelectedP
    WHEN 'P1' THEN P1
    WHEN 'P2' THEN P2
    WHEN 'P3' THEN P3
    WHEN 'P4' THEN P4
    WHEN 'P5' THEN P5
  END
    AS SelectedPValue
FROM
  yourTable

This will return NULL for anything not mentioned in the CASE expression.

EDIT:

An option with just a little less typing...

SELECT
  id, SelectedP, val  
FROM   
  yourTable AS pvt
UNPIVOT  
(
  val FOR P IN   
  (
    P1,
    P2,
    P3,
    P4,
    P5
  )
)
  AS unpvt
WHERE
  SelectedP = P

NOTE: If the value of SelectedP doesn't exist in the UNPIVOT, then the row will not appear at all (unlike the CASE expression which will return a NULL)

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b693738aac0b594cf37410ee5cb15cf5

EDIT 2:

I don't know if this will perform much worse than the 2nd option, but this preserves the NULL behaviour.

(The preferred option is still to fix your data-structure.)

SELECT
  id, SelectedP, MAX(CASE WHEN SelectedP = P THEN val END) AS val
FROM   
   yourTable AS pvt
UNPIVOT  
(
  val FOR P IN   
  (
    P1,
    P2,
    P3,
    P4,
    P5
  )
)
  AS unpvt
GROUP BY
  id, SelectedP

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f3f64d2fb6e11fd24d1addbe1e50f020