Generate three rows in select query

Viewed 166

I have following table

SELECT TableCode, Col1, Col2
FROM TableA
WHERE TableCode = 23

Result of Table:

TableCode   |  Col1    |   Col1
23          | CustCode |    QS
23          | CatCode  |    QS

After that i wrote one query on TableA which return following output

Query :

SELECT TableCode,x.ColCode,
       x.ColumnName + '_' + CONVERT(VARCHAR(5), ROW_NUMBER() OVER (PARTITION BY X.COL ORDER BY X.COL)) [ColumnName],X.Values,
       ROW_NUMBER() OVER (PARTITION BY X.COL ORDER BY X.COL) [RowNo]
FROM TableA a CROSS APPLY   
     (SELECT 1 ColCode,'ParaName' ColumnName,Col1 Values
      UNION ALL  
      SELECT 2,'ParaSource',Col2   
     ) x
WHERE TableCode = 23;

Result :

TableCode | ColCode | ColumnName | Values |   RowNo
23       |   1     | ParaName_1 | CustCode |    1
23       |   1     | ParaName_2  | CatCode |    2
23       |     2   | ParaSource_1 | QS     | 1 
23        |   2    | ParaSource_2 | QS     | 2

And i required following output:

Required Output :

TableCode | ColCode | ColumnName | Values |   RowNo
23       |   1     | ParaName_1 | CustCode |    1
23       |   1     | ParaName_2  | CatCode |    2
23       |   1     | ParaName_3  | Null |   3
23       |     2   | ParaSource_1 | QS     | 1 
23        |   2    | ParaSource_2 | QS     | 2
23        |   2    | ParaSource_3 | null   | 3
2 Answers
Related