Generate three rows dynamically based on data

   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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mitesh Gadhiya9y ago
If you will add one row with NULL value then you will get your expected output.

Please have a look this query, it may help you
;With TableA as (
select 23 as TableCode,
'CustCode' as Col1,
'Qs' as Col2
UNION
select 23 as TableCode,
'CatCode' as Col1,
'Qs' as Col2
UNION
select 23 as TableCode,
NULL as Col1,
NULL as Col2
)


Select TableCode,x.ColCode,X.ColumnName,X.[Values],ROW_NUMBER() OVER (PARTITION BY X.ColumnName ORDER BY X.ColumnName) [RowNo] FROM TableA
a CROSS APPLY
(SELECT 1 ColCode,'ParaName' ColumnName,Col1 as 'Values'
UNION ALL
SELECT 2,'ParaSource',Col2
) as x
0

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