2

I have this table of stage-wise activities and their corresponding flags.

STAGE |  ACTIVITY | FLAG      |  STATUS  |
S1    |  A1       |S1_A1_FLAG | ST_S1_A1 |
S1    |  A2       |S1_A2_FLAG | ST_S1_A2 |
:        :            :          :
SN    |  A1       |SN_A1_FLAG | ST_SN_A1 |
SN    |  A2       |SN_A2_FLAG | ST_SN_A2 |
:     |  :        |   :       |  :       |
SN    |  AN       |SN_AN_FLAG | ST_SN_AN |

I have to create a view that will have following structure...

STAGE|   A1     |     A2       | ... | AN         |
---------------------------------------------------
S1   |S1_A1_FLAG|  S1_A2_FLAG  | ... | S1_AN_FLAG |
     | ST_S1_A1 |   ST_S1_A2   | ... |  ST_S1_AN  |
----------------------------------------------------
S2   |S2_A1_FLAG|  S2_A2_FLAG  | ... | S2_AN_FLAG |
     | ST_S2_A1 |   ST_S2_A2   | ... |  ST_S2_AN  |
----------------------------------------------------
:          :           :           :       :
----------------------------------------------------
SN   |SN_A1_FLAG|  SN_A2_FLAG  | ... | SN_AN_FLAG |
     | ST_SN_A1 |   ST_SN_A2   | ... |  ST_SN_AN  |

Here 'flag' and 'status' are string values and have to be displayed in a single cell.

Also, using "hard-coded" case-when statements are not a viable option as there are a few hundred stages each containing at least a dozen activities. As I am new to pivots in sql, any help regarding the same would be welcome

jit thakore
  • 95
  • 7

1 Answers1

2
SELECT * from
(select Stage,Activity, flag+' ' + astatus as AStatus 
 FROM tblStage
)tb
PIVOT
(
  Min(AStatus)
  FOR Activity IN([A1],[A2])
)p;

Check these :

  1. To Pivot on dynamic columns.

  2. Also Check Pivot on Varchar Values.

Community
  • 1
  • 1
user1331032
  • 232
  • 1
  • 3
  • 12