You must Sign In to post a response.
  • Category: .NET

    Generate dynamic datatable from Dataset

    Dear Friends,

    I am generating the following Dataset throug Code:
    Account Item Price
    Stationary Paper 200
    Stationary Pen 20
    Stationary Steplar 100
    Office Table 1000
    Office Chair 500
    Office Printer 5000



    Now my requirement is to generate a Datatable with the below exapmle

    Description Paper Pen Steplar Table Chair Printer Total
    Stationary 200 20 100 340
    Office 1000 500 5000 7000

    It will be purely dynamic
    Hope you understand the situation.

    Thanks.
    Binay Kumar Prasad
  • #764966
    Hi,

    I am attaching example file for your reference

    CreateDataTable-fromDataSet.xlsx

    Delete Attachment

  • #764976
    From which datasource you would like to read the data. Is it the database or Excel document or any notepad or word document? Based on the datasource we can write the code to get the data from.
    Miss. Jain
    Microsoft Certified Technology Specialist in .Net

  • #764980
    Hi,

    I have mentioned in the first line that the datasource is Dataset generated from database through stored procedure.

    With regards,
    Binay Kumar Prasad.

  • #765007
    Hi,

    If you are trying to achieve the same using DataSet, you need to put more efforts to achieve it, rather than doing changes in DataSet my suggestion is doing the same in SQL query side by using PIVOT Table you can easily achieve your task.

    Refer below link, to know more about PIVOT Table "codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query"

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #765057
    Hi,
    Try this:
    declare @t1 table(Account varchar(50),Item varchar(50),Price varchar(50))
    insert into @t1 (Account, Item, Price) values
    ('Stationary', 'Paper' ,200 ),
    ('Stationary', 'Pen' ,20 ),
    ('Stationary', 'Steplar' , 100 ),
    ('Stationary', 'Eraser' ,20 ),
    ('Office', 'Table' ,1000 ),
    ('Office', 'Chair' ,500 ),
    ('Office', 'Printer' ,5000 ),
    ('Office', 'Cartiredge' ,500 )

    SELECT [account], [Paper], [Pen] , [Steplar], [Eraser], [Table], [Chair] ,[Printer] ,[Cartiredge]
    INTO [@UnpivotExample]
    FROM @t1
    PIVOT
    (
    max( price)
    FOR [item] IN ([Paper], [Steplar], [Eraser], [Table], [Pen] ,[Chair],[Printer] ,[Cartiredge] )
    ) AS P
    GO
    Update [dbo].[@UnpivotExample] Set [Total] = (select Top 1
    (CAST( case Paper when NULL then 0 else Paper end as int))+
    CAST( case Pen when NULL then 0 else Pen end as int) +
    CAST( case Steplar when NULL then 0 else Steplar end as int)+
    CAST( case Eraser when NULL then 0 else Eraser end as int)
    from [@UnpivotExample] ORDER BY Account desc) WHERE Account = 'Stationary'
    GO
    Update [dbo].[@UnpivotExample] Set [Total] = (select Top 1
    (CAST( case [Table] when NULL then 0 else [Table] end as int))+
    CAST( case [Chair] when NULL then 0 else [Chair] end as int) +
    CAST( case [Printer] when NULL then 0 else [Printer] end as int)+
    CAST( case [Cartiredge] when NULL then 0 else [Cartiredge] end as int)
    from [@UnpivotExample] ORDER BY Account asc) WHERE Account = 'Office'
    GO
    SELECT * FROM [dbo].[@UnpivotExample] ORDER BY [account] desc
    GO
    Hope it helps.
    Regards,
    Shashikant Gurav
    shashikantgurav22@gmail.com

  • #765060
    Hi Shashukant,
    Thanks for your feedback but here columns are dynamic. And totally based on user selection. So can not hard code columns. Is there any way to make columns dynamic?


Sign In to post your comments