How to convert Rows as columns in c#.net either datatable or database or list of object

Question

User-2103114970 posted

I have input data  from database

 

TableName	RowNo	CoulumnName	ColumnValue
Employee	1	EmpNo	100
Employee	1	EmpName	Sam
Employee	1	Salary	100000
Employee	1	EmpNo	100
Employee	2	EmpNo	101
Employee	2	EmpName	Houston
Employee	2	Salary	200000
Department	1	DeptNo	200
Department	1	DeptName	IT
Department	2	DeptNo	201
Department	2	DeptName	BPO

Need Output:

 

TableName	RowNo	EmpNo	EmpName	Salary
Employee	1	100	Sam	100000
Employee	2	101	Houston	200000

 

 

 

TableName	RowNo	DeptNo	DeptName
Department	1	200	IT
Department	2	201	BPO

Could any one help me on this.

Thanks,

Murali.

Answer 1

User2103319870 posted

mkonanki

I have input data  from database

One option is to fetch value from database in format you need. Sample Query to get value from database as per your requirement

To get data from Employee Table

SELECT TableName,RowNo,
  MAX(CASE WHEN COLUMNNAME='EMPNO' THEN COLUMNVALUE END) EmpNo,
  MAX(CASE WHEN COLUMNNAME='EMPNAME' THEN COLUMNVALUE END) EmpName,
  MAX(CASE WHEN COLUMNNAME='Salary' THEN COLUMNVALUE END) Salary
FROM YOURTABLE
where TableName = 'Employee'
GROUP BY RowNo,TableName

To get data from Department Table

SELECT TableName ,RowNo,
  MAX(CASE WHEN COLUMNNAME='DeptNo' THEN COLUMNVALUE END) DeptNo,
  MAX(CASE WHEN COLUMNNAME='DeptName' THEN COLUMNVALUE END) DeptName
FROM YOURTABLE
where TableName = 'Department'
GROUP BY RowNo,TableName

• Sample Working Demo

Answer 2

User-2103114970 posted

A2H, Thank you so much for your help .

Need one more help on this , if i add a new column 'colum,error' and need to  replace  column value when  'columnerror ' contains error information.

TableName	RowNo	CoulumnName	ColumnValue	ColumnError
Employee	1	EmpNo	100	Invalid number 100
Employee	1	EmpName	Sam

 

Thanks,

Murali.

 

Output:

TableName	RowNo	EmpNo	EmpName
Employee	1	Invalid number 100	Sam

Answer 3

User2103319870 posted

if i add a new column 'colum,error' and need to  replace  column value when  'columnerror ' contains error information.

You can add one more Case condition like below

SELECT TableName,RowNo,
  MAX( CASE WHEN COLUMNNAME='EMPNO' THEN CASE WHEN ColumnError <> '' THEN ColumnError ELSE ColumnValue END END) EmpNo,
  MAX(CASE WHEN COLUMNNAME='EMPNAME' THEN CASE WHEN ColumnError <> '' THEN ColumnError ELSE ColumnValue END END) EmpName,
  MAX(CASE WHEN COLUMNNAME='Salary' THEN CASE WHEN ColumnError <> '' THEN ColumnError ELSE ColumnValue END END) Salary
FROM YOURTABLE1
where TableName = 'Employee'
GROUP BY RowNo,TableName

Answer 4

User-2103114970 posted

Thank you so much, great answer. Appreciate your help. 

Thanks,

Murali.