Bind data from database and rotate / transpose data in C#

Question

0.00/5 (No votes)

See more:

, +

Hello Everyone,

I want to retrieve data from SQL database and bind to control(Datagridview / Datalist). Make a table format output and it will shows Rows as column and column as rows.

I have one table: tbl_CustData with two fields such as FixedDepDate(datetime datatype) and another is Month_year (nvarchar datatype). The values are as follows:

FixedDepDate | Month_year
2018-07-03 00:00:00.000 | March-18
2018-08-23 00:00:00.000 | August - 2018
2018-08-29 00:00:00.000 | August - 2018
2018-07-04 00:00:00.000 | July - 2018
2018-07-10 00:00:00.000 | July - 2018
2018-07-25 00:00:00.000 | July - 2018

Expected Output is :

March - 18 | 03
July - 18 | 04 | 10 | 25
August - 18 | 23 | 29

I already tried following methods but not getting output which is I am expecting.
1) Used Datalist/Gridview control and tried to bind data
2) Used PIVOTE operator but not getting output.

Also please give some idea which control is suitable to bind the data and show on my webpage or else any technique to resolve this problem.
Note: I am not using any events to show my output. Want to display my output directly on my page after run the project.

Thanking you in advance and waiting for your valuable solution.

What I have tried:

I want to Bind data from database and rotate / transpose data.

Posted 4-Jul-18 1:59am

Pankaj sonar

Updated 5-Jul-18 2:07am

v2

Add a Solution

2 solutions

Solution 2

im giving you one example you need to check at your end how to sonsolidate this things into your code

SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _
InvoiceAmount as Amount FROM Invoice) as InvoiceResult

Suppose you have data with
Year month amount
2010 Jan 100
2011 Feb 90
2010 Feb 110
Then use pivot table on sql query like below

SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt

output will be

year Jan Feb
2010 100 110
2011 90

Please check at your end i hope you got help from this.

Posted 5-Jul-18 2:00am

Member 12893295

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 12893295 · Accepted Answer · 2018-07-04T05:53:00

Solution 1

You need to check pivot which convert the value as same whateve you want on database side

Posted 4-Jul-18 5:53am

Member 12893295

Comments

Pankaj sonar 5-Jul-18 1:49am

Can you make SQL query using PIVOTE operator for expected output? Please help..!!