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

  • 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.

    Monday, October 8, 2018 10:44 PM

Answers

  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 9, 2018 12:28 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 9, 2018 4:56 PM

All replies

  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 9, 2018 12:28 AM
  • 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

     

     

     

     

     

     

     

     

    Tuesday, October 9, 2018 4:23 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 9, 2018 4:56 PM
  • User-2103114970 posted

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

    Thanks,

    Murali.

    Tuesday, October 9, 2018 5:05 PM