Forums - C# Corner

Forum guidelines
Vinayak Gurav

Vinayak Gurav

  • 1.5k
  • 3
  • 123

Generate three rows dynamically based on data

Jul 24 2017 8:33 AM
   
   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


Answers (1)