SELECT TableCode, Col1, Col2
FROM TableA
WHERE TableCode = 23
| TableCode | Col1 | Col2 |
| 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_2 | Null | 3 |