I have a table that could look like the left table in the following picture, but i want to present it as the right table in the picture. http://pici.se/549963/?size=fullsize
So i've found out that i could use pivots to do this, but am having some trouble getting it to work, so if you know how to do this.
i have a table for customer forecast which contains customer, week number and quantit as columns. now i need to write query and list the week as column for each customer and each cell should have a quantity. how to write query for this using pivot logic. i dont want to use cursor.
View 3 RepliesI have records with and without a email entered in the field called 'email'.
This one works:
Return From p In db.myTable _
Where p.Email.Length > 0 _
Select New With {.p.Name, p.Email}
but this doesn't
Return From p In db.myTable _
Where p.Email.Length = 0 _ ( nor does Where NOT p.Email.Length > 0 )
Select New With {.p.Name, p.Email}
Am I missing something?
I want this result
Id
Name
Service [code].....
This query does not return any record
[code]....
Is it possible to get a Activity, year, monthly pivot from a table for a clientID? And I need to show it in a control (grid or ?)
My table has tblActivities (ActivityID, ActivityDate, ClientID)
And I have a lookup tblLookupActivities (ActID, Activity)
I need something that looks like:
Activity | Year | Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Nov|Dec|
CPR | 2007 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 3 |
Jump| 2008 | 2 | 1 | 1 | 1 | 0 | 2 | 1 | 1 | 0 | 0 | 0 | 1 | 3 |
I tried to develop a dynamic pivot query with following code to avoid hard coding. I can got the 'Q1, Q2, Q3, Q4' dynamically and assigned to @(@quarter. But it cannot be passed to the pivot query.
[Code]....
I have a database with a Referrals table connected to a Facilities lookup table. I'd like to create a report using ReportViewer in my asp.net web application that shows the number of referrals for each month in a given date range for each Facility. So the report would look like (for the date range 1/1/2010 - 4/30/2010):
[code]....
I am using following pivot query but not getting result as single row getting as 4 rows.
One more problem i am unable to pass dates as parameters to storeprocedure something like this
select [@date] .
SELECT 'Forecasted' AS HeadCount,
[8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010]
FROM
(SELECT *
FROM TblEmpCount) AS SourceTable
PIVOT
(
SUM(EmpCount)
FOR StartDate IN ([8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010])
) AS PivotTable;
I am getting result as 4 rows but i want result in single row like this
HeadCount 8/1/2010 8/8/2010 8/15/2010 8/222/2010
Forecasted 191 182 176 169
I'm assuming I need to use a pivot table from what I've found so far. However, every one that I've seen has not had the date setup like I have.
I have a Gridview on my page which I need to be populated like so
Date NCMR
1/1/10-1/31/10 #
2/1/10-2/28/10 #
I want to do this for 10 months... bottom record being the current month (or last month). I would also like the Date to format to January 2010 I used the above date format because that is how it is in the table. Basically, I need to know how to create the pivot table to search the table for 1/*/10, 2/*/10, etc
1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like
SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.
Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).
2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query
(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).
If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.
[code]...
This query works perfectly on the query analyser.
But when configuring the Table adapter ,I try executing the query and i get 0 rows affected.
What could I be getting wrong in this case.
NB:Existing GalleryID has been supplied.
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
View 10 Repliesselect Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 RepliesI've got a query such as
Dim MediaQuery =
From m
In dB.DOWNLOADS _Where m.ID = id _Select
which returns a record from the database. One of the fields in the record is a FK to another table. I need to read this value to be able to set a dropdown based on the ID but I can't work out how to access it. For a standard record I can just do the following txtTitle.Text = MediaQuery.FirstOrDefault().TITLE
However with the foreign key it doesn't work like that. I've tried drpGroup.SelectedIndex = MediaQuery.FirstOrDefault().DOWNLOAD_GROUPS.ID where DOWNLOAD_GROUPS is the FK field but it returns Object reference not set to an instance of an object. If you're simply wanting to read some values from a single db record in the entitiy framework and one is a foreign key how should I go about getting the value?
I'd like to pull several NewValues from a FormView and write them to a database as part of the Update command.For some reason, I am unable to do this using the following code. Is there something I am missing?
[Code]....
So I've been teaching myself ASP.NET and VB for about 3 weeks now, having trouble with datatables, not sure how to get the rows to line up like I think they should. My code:
[Code]....
Which gives me the data output I want, but the days are adding rows underneath the previous day instead of lining up side-by-side. The idea is that once it's working I'll replace the specific dates with calendar controls. The gridview currently outputs like this:
I need to display an Excel like Pivot table in ASP.Net web.I knew there is OWC Pivot table but it looks really ugly and not like Excel's.
Is there any control out there or we can create one of our own ?
I have a doubt and i am sending you the details below, what i want to display in Gridview.
below i am describing the sql Table Structure.
Original Table Structure.
ID               Date            TimeLogged(Hrs)       UserName
1Â Â Â Â Â Â Â Â Â Â Â Â 10/8/2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 5.50Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Bubai
2Â Â Â Â Â Â Â Â Â Â Â Â 11/8/2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2.30Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Bubai
3Â Â Â Â Â Â Â Â Â Â Â Â 10/8/2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3.30Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Bhanu
4Â Â Â Â Â Â Â Â Â Â Â Â 11/8/2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7.30Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Bhanu
I want result like below. User Name should be dynamic. May be lot of users. User name will come from Database table.
i want to show details in Gridview(Front End).
Date                   Bubai              Bhanu                   Total
10/8/2012Â Â Â Â Â Â Â Â Â Â Â Â Â 5.30Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 8. 30
11/8/2012Â Â Â Â Â Â Â Â Â Â Â Â Â 2.30Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7.30Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
 Total                  8                   10.30                 18.30
Â
I have a doubt and i am sending you the details below, what i want to display in Gridview. below i am describing the sql Table Structure.Original Table Structure.
ID Date TimeLogged(Hrs) UserName
1 10/8/2012 5.50 Bubai
2 11/8/2012 2.30 Bubai
3 10/8/2012 3.30 Bhanu
4 11/8/2012 7.30 Bhanu
I want result like below. User Name should be dynamic. May be lot of users. User name will come from Database table. i want to show details in Gridview(Front End).
Date Bubai Bhanu Total
10/8/2012 5.30 3 8. 30
11/8/2012 2.30 7.30 10
Total 8 10.30 18.30
I am using Querystring "ddl4" to pass on value of a drop down list to the next page where I am using that value in the WHERE clause of a SQL command. I also need to check whether ddl4 is blank or not which I do using If Else in SQL command. I am not able to run the query where the Querystring ddl4 IS NULL
This is the sample link with the Querystring ddl4 carrying a blank value:
[code]....
I'am developing a attendance software of employees with visual studio 2005 and sql server 2005, in which i have to display the attendance in a gridview withname and date as follows,
NAME (Date)1 2 3 4 5 6 7 8 9 10 .... ... 31
RAM P A P P P A
P- Present
A- Absent
I am bringing back a matrix from using a pivot table and displaying the results in a gridview. Here is the code to my gridview
[Code]....
This works fine but I want to total each column but they are dynamic so I don't know how many columns there will be. So it will look something like below:
East Midlands
0
0
0
0
0
0
0
0
0
North East
0
0
0
0
0
0
0
0
0
North London
0
0
0
0
0
0
0
0
0
North West
0
0
0
0
0
0
0
0
0
ROI
0
0
0
0
0
0
0
0
0
Scotland & NI
0
0
0
0
0
0
0
0
0
South East
1
1
0
0
0
0
0
0
0
South London
0
0
0
0
0
0
0
0
0
South Wales & West
0
0
0
0
0
0
0
0
0
West Midlands
0
0
0
0
0
0
0
0
0
Total
1
1
0
0
0
0
0
0
I am struggling to get the column totals in the footer. I have the code below which just brings back zero each time and if i move the Dim total as integer = 0 to the top as a global variable I get 54 in each of the total columns?
[Code]....
Need to bind repeater using PIVOT method via stored procedure.
View 1 RepliesI have one database in which student enroll no with his sem is stored . I want that if i enter stud enroll no then in dropdown list all the sem related to that stud should come. My databaseÂ
Stud enroll no     sem1      sem2       sem3      sem4
    1          July 2013   Jan 2014    July 2014   Jan 2015
    2          Feb 2013   Aug 2014    Feb 2014   Aug 2015Â
     3         July 2012   Jan 2013   July 2013   Jan 2014
I have one query in my project (saved in folder SQLQueryes, Query.sql) and i need to execute it. Also, i have one parameter in script and i need to include him also. How?
View 8 Replies
