SQL Server :: How To Group The Data Query
Dec 22, 2010
I am using SQL Server 2005. I have below table:
CompanyNo CompanyName Product
--------------------------------------------------------
100 C1 P11
100 C1 P12
100 C1 P13
200 C2 P21
200 C2 P22
etc...
And I want my sproc returns like...
CompanyData
------------------
100 - C1 - P11, P12, P13
200 - C2 - P21, P22
etc...
View 3 Replies
Similar Messages:
Mar 26, 2010
I'm using a linq group by query (with two grouping parameters) and would like to use the resulting data in a nested repeater.
var dateGroups = from row in data.AsEnumerable()
group row by new { StartDate = row["StartDate"], EndDate = row["EndDate"] };
"data" is a DataTable from an SqlDataAdapter-filled DataSet. "dateGroups" is used in the parent repeater, and I can access the group keys using Eval("key.StartDate") and Eval("key.EndDate").
Since dateGroups actually contains all the data rows grouped neatly by Start/End date, I'd like to access those rows to display the data in a child repeater. To what would I set the child repeater's DataSource? I have tried every expression in markup I could think of; I think the problem is that I'm trying to access an anonymous member (and I don't know how.) In case it doesn't turn out to be obvious, what would be the expression to access the elements in each iteration of the child repeater? Is there an expression that would let me set the DataSource in the markup, or will it have to be in the codebehind on some event in the parent repeater?
View 3 Replies
Jan 13, 2011
I am using a nested listview to display a seperate grid for each group of data returned from my db query. To get this working, I have adapted a piece of code to group the data prior to it being bound:
[Code]....
I am then using the following html markup:
[Code]....
[Code]....
This works as I want it to however some of the fields within the nested table need to be formatted as currency so I am trying to use Eval and {0:c} to do this however the moment I use Eval, the data items cannot be found
DataBinding: 'System.Data.DataRow' does not contain a property with the name '0'.
View 4 Replies
Jul 7, 2010
I'm working on a portal, and I need to check users conection on the ldap.
And if users are members of a special group then it works.
I've exactly used this link to make it works : http://msdn.microsoft.com/en-us/library/ff649227.aspx (almost work)
My point is : my ldap path seems good, no error anymore, when I log with my username and password.But when I querry it, it returns null, but I'm sure my user is in the group :
// Bind to the native AdsObject to force authentication. Object obj = entry.NativeObject; DirectorySearcher search = new DirectorySearcher(entry); search.Filter = "(SAMAccountName=" + username + ")"; search.PropertiesToLoad.Add("cn"); SearchResult result = search.FindOne(); if(null == result) { return false; }
I'm a normal user, i test with my account. I'm a member of the group that I'm looking for.
View 8 Replies
Mar 4, 2010
Stack-Overflow :3 I've got a kind of hard-case question.
So I'll try clearly explain my idea with my poor english :/ need select to DataTable SOME values from ???_**GetDisplayData **procedure for each day of previos month (GROUP IT) where group rules for different columns is different
I need to select some values in stored procedure from other stored procedure like this :
SELECT X FROM Y_Procedure(@ProcedureParameters)Also I need to select from dynamic SQL Procedure like
Y_Procedure=@Y+'_Procedure'
SELECT X FROM Y_Procedure(@ProcedureParameters)
Also I need to load it to DataTable :-/INSERT INTO @Report (CellHorizontal, CellVertical, CellValue) --to TABLE
SELECT Date,X2,X3 FROM Y_GetDisplayData(@Param)
SET NOCOUNT OFF;
SELECT *
FROM @Report
GetDisplayData works as select with parameters and that doesn't returns SQL DataTable
And there is no way to recode GetDisplayData's procedures, it's just constant procedures for me.Finally I need to Group nodes from this table
INSERT INTO @Report (CellHorizontal, CellVertical, CellValue)
SELECT T1.Date,
IF ((Select grouptype...)=1) T1.X2 + T2.X2
ELSE IF ((Select grouptype...)=2) AVG(T1.X2,T2.X2),
IF ((Select grouptype...)=1) T1.X3 + T2.X3
ELSE IF ((Select grouptype...)=2) AVG(T1.X3,T2.X3),
(SELECT T2.Date,X2,X3 FROM Y_GetDisplayData(@Param) T2
WHERE T2.Date>T1.Date AND T2.Date>=T1.Date)
FROM Y_GetDisplayData(@Param) T1
GROUP BY EVERY DAY ???--and here is epic fail
I can make all stuff on asp server :And C# allows me to use something like SelectCommand = IzmProc + "_GetDisplayData"; And then I will work (select special data) with DataTables on ASP.NET Server but it's better to make all on SQL ... But looking like it's just unrealizable on SQL >_<
I gonna think about C# realization,but my code is very weird , got errors and doesn't works >_<
public static DataTable GetReport(string Param)
{
System.Configuration.ConnectionStringSettings connSetting = ConfigurationManager.ConnectionStrings["FlowServerConnectionString"]; [code].....
View 3 Replies
Jan 18, 2011
We have a webservice which stores data in sql server 2008R2.I am trying to build a billing system and struggling with the next sql query.
scriberID,X.SubscriberName,X.ProductID,X.ProductDesc,X.NumOfTransactions,X.UnitPrice,SUM(X.NumOfTransactions
* X.UnitPrice)
AS ExtendedPrice
[code]...
View 15 Replies
Mar 26, 2011
I'm currently learning entity framework (from the Julia Lerman book "Programming Entity Framework") and got really confused at the grouping part.
var contacts = from c in context.Contacts
group c by c.Title into myGroup
select myGroup;
This is supposed to return a Grouping<K,T> type, but the book didn't give an example on how exactly do I access this "contacts" query and I can't figure it out. When I did foreach(var contact in contacts), my "contact' variable has no knowledge of any of the properties of the Contact type (contact.FirstName etc).
View 1 Replies
Jan 8, 2010
i have the following objects:
Event:
- EventID (PK)
- GroupID (FK)
- StartDate
Group:
- GroupID (PK)
- GroupName
What i need to do is select the last 5 events for each group using LINQ to Objects. I tried starting with:
[Code]....
But i'm not sure how i only take the last 5 per group.
The simple way of doing this is to get the groups and then have a property on that which returns the last 5 events but if i databind this i will have to use a nested Repeater/ListView. Ideally i would like to have a row foreach event.
View 3 Replies
Sep 16, 2010
how to group weekly data for a month days
View 6 Replies
Feb 5, 2010
I'd like to gruop data from a table, for example, by DATE or CATEGORY, something like this:
29/01/2010
News 1
News 2
News 3
30/01/2010
News 1
News 2
News 3
or group by CATEGORY, like this:
ASP.NET
News 1
News 2
News 3
PHP
News 1
News 2
News 3
View 4 Replies
May 6, 2010
I have a table that contains image data type and has more than 20,000 records.I want to change its file group to the new file group. I tried to change it using table designer, but everytime I wanted to save it, it occured an error with this message:
[Code]....
Does anyone know how to change file group using Alter Table query? I'm using SQL Server 2005.
View 2 Replies
May 4, 2010
i have 2 tables company(companyId,companyName,City,state,country) and people(peopleId,peopleName,City,state,country) i want to display the total no of contacts and Total no of companies with respect to countries
i tried with group by: select country,count(*)companyId from company group by country is displaying the total number of companies with respect to country select country,count(*)peopleId from people group by country is displaying the total number of peoplewith respect to country
but i want to combine these two quries i want the output as :
Country Companies Contacts
India 10 5
USA 0 10 (usa is having only contacts ,i doesn' have the companies)
UK 8 0 (ukis having only companies ,i doesn' have the contacts)
View 2 Replies
Oct 26, 2010
I need to develop a page in my applicaiton like the image for the user lists.
I need to get the list of users under a group and need to show likw this.
View 4 Replies
Jan 22, 2011
I want to transfer the data from one server to another by sql query. I want query. I dont know how to write?
View 2 Replies
May 17, 2010
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 Replies
Mar 2, 2011
I am trying to create statitics for game downloads and am having dificulty with a sub part of my query. the goup by command is not having the disired result and i think i have been looking at it too long to see what i am doing wrong. [Code]....
At the moment it is producing each result in many rows when only 4 rows should appear. its the download column that is doing it. needs to be sub queried or something to display total in relation to month,gameid,userid.
Month-download-GameID-UserID
1 1
1 1
1 1
1 1
View 2 Replies
Feb 1, 2011
I am trying to display this query result in an aspx page.
It is very slow in loading. Here is the query. The inner query inside the outer quesry is the problem.(Please see the underlined part in the query) - (If I remove that part it is very fast.)
select
top 500
--This column is the issue
,Governing_Class=( case when exists (select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.appid and tqc.class_code not in('8742' ,'8810','7380')
order by tqcm.premium_amt desc
)
then ( select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.
appid
order by tqcm.premium_amt desc
)
......... From tables
View 7 Replies
Sep 3, 2010
I am a biggner in SQL DB . but i started a complicated and painfull work in SQL SERVER 2008. the problem convert Oracle hierarchical query to SQL query. the query
SELECT DISTINCT
LEVEL LVL,
SCH.NSCHEDULE_SL,
SCH.NSCHEDULE_SL_FM,
SCH.CSHED_CNAME
FROM FA_SCHEDULES SCH
WHERE LEVEL = 1
AND NSCHEDULE_SL_FM IS NULL
AND NBRANCH_SL = 2
CONNECT BY PRIOR SCH.NSCHEDULE_SL_FM = SCH.NSCHEDULE_SL
AND NBRANCH_SL = 2
View 1 Replies
Mar 30, 2011
I have a query with group-by clause. I want to use it in recusionCTE.How is it possible?I want to know the totalamount for each fname done through this query:select top 10 fname,SUM(totamt) as 'Total Amount' from tables_list group by fname order by 'Total Amount' descBut when any person logins he must be able to see this list for his own hierarchy down the line including himself.
View 2 Replies
Jan 20, 2011
select a.* from (select vip,date,amount,dense_rank() over(order by vip,date) as 'sn' from income ) a where sn = 1
vip date amount sn
0001 2010/1/1 50 1
0001 2010/1/1 10 1
0001 2010/1/6 80 2
0001 2010/1/7 90 3
0002 2010/1/2 40 4
0002 2010/1/5 10 5
0002 2010/1/5 80 5
0002 2010/1/7 90 6
would it possible give it row number for each group? (group by vip) like this
vip date amount sn
0001 2010/1/1 50 1
0001 2010/1/1 10 1
0001 2010/1/6 80 2
0001 2010/1/7 90 3
0002 2010/1/2 40 1
0002 2010/1/5 10 2
0002 2010/1/5 80 2
0002 2010/1/7 90 3
View 2 Replies
Oct 15, 2010
I have this statement:
[Code]....
The problem is, it is not ordered correctly. How can I order it correctly with earliest datetime on top?
View 5 Replies
Oct 13, 2010
How would I go about using my session data (ie username) for an SQL query to populate a dropbox? Here is what I have so far: SELECT Client_Name, Client_ID FROM Client WHERE (Client_Name LIKE '%@Client_Name%')
View 6 Replies
Jan 3, 2011
I have a table articles where i keep all the records of the articles wriiten by people online. Now i need to retrieve related articles based on the title i guess. I tried doing that using my stored procedures with the LIKE operator, but i guess that doesn't work because it does not take the whole string of the title and match with the ones in the database.
View 2 Replies
Mar 22, 2011
I have a stored procedure which is composed of three unions of three different views.
After the views are Unioned together, they are placed into a temporary table.
I need to somehow keep track of the data which comes from the second view, "View2".
Specifically I will later need to compare all Ages of people from the second view.
How can I do this? Here is an example of my unioned views
Select Name,Age,Gender from View1
UNION
Select Name,Age,Gender from View2
UNION
Select Name,Age,Gender from View3
View 4 Replies
Oct 26, 2010
i'm trying to fetch data from xml in the sql query.
DECLARE @xmlDelete xml
SET
@xmlDelete =
'<DocumentElement>
<row FSFF_FeesItemID="185" FSI_ID="104" />
<row FSFF_FeesItemID="187" FSI_ID="104" />
<row FSFF_FeesItemID="187" FSI_ID="105" />
</DocumentElement>'
;
WITH
r_set AS
(
SELECT
T1
.c1.value('(./FSFF_FeesItemID/text())[1]',
'int')
AS [id1]
,
T1
.c1.value('(./FSI_ID/text())[1]',
'int')
AS.......................................
View 2 Replies
