Create Function to return the all names(seperated by comma) for a given code. Create FUNCTION [dbo].[GetNamesById] ( @code int ) RETURNS varchar(max) AS BEGIN declare @result varchar(max) select @result = COALESCE(@result + ',', '') + [name] from tblCode where code = @code return @result END GO now use following query: select code,dbo.GetNamesById(code) from tblCode group by code above query will give output as: Code name 50141 abc,pqr

What best you can do is to create one table like ID DepartMent DepartMentData and make DepartMentData column as xml type where you will store data in the hierarchal way. For eg. There is an admin department so the DepartMentData column may have values like <Department Name = "Admin"> ------<SupportTeams> ----------<SupportUser Name ="SupportUser1" ID="1"> --------------<DeveloperTeam1> ------------------<User Name ="Dev1User1" ID="1"/> ------------------<User Name ="Dev1User2" ID="2"/> --------------</DeveloperTeam1> ----------</SupportUser> ----------<SupportUser Name ="SupportUser2" ID="2"/> --------------<DeveloperTeam2>; ------------------<User Name ="Dev2User1" ID="1"/> ------------------<User Name ="Dev2User2" ID="2"/> ---------------</DeveloperTeam2> -----------</SupportUser> ------</SupportTeams> </Department > Now in C# code you could pass the element you want to read Like for eg. you want to read all the users below SupportTeams then you just need to call: XmlNodeList ChilNodes = doc.GetElementsByTagName("SupportTeams") This will return all the child nodes including SupportTeams node. Now you can iterate through for each node foreach (XmlNode cNode in ChilNodes ) { } In the loop check that each node has further any child node by calling: if(cNode.ChilNodes!= null) This way you could ensure that you have not gone to above in the tree hierarchy. This will always go to below in the tree hierarchy.

Hi the child xml nodes are invalid. you need to add element name to the child element nodes. Below format is be the correct one : <Failure_Table> <Failure Failure_ID="1" Name="Failure_1" Description="TBD" /> <Failure Failure_ID="2" Name="Failure_2" Description="TBD" /> <Failure Failure_ID="3" Name="Failure_3" Description="TBD" /> </Failure_Table> And here is the C# code : string _xmlData = "<Failure_Table>" + "<Failure Failure_ID=\"1\" Name = \"Failure_1\" Description = \"TBD\" />" + "<Failure Failure_ID=\"2\" Name = \"Failure_2\" Description = \"TBD\" />" + "<Failure Failure_ID=\"3\" Name = \"Failure_3\" Description = \"TBD\" />" + "</Failure_Table>"; int desiredFailure_ID = 2; string name; XmlDocument doc = new XmlDocument(); doc.LoadXml(_xmlData); XmlNodeList list = doc.SelectNodes(@"//Failure"); foreach (XmlNode pNode in list) { int failure_ID =int.Parse(pNode.Attributes["Failure_ID"].Value); if (failure_ID == desiredFailure_ID) name = pNode.Attributes["Name"].Value; }

Hi, I have below records in my table 'Sales' ID SalesName LastUpdated ------------------------------------------------- 1 S1 2009-07-16 13:27:23.890 2 S2 2009-08-16 13:32:58.127 3 S3 2009-08-16 13:33:01.987 4 S4 2009-08-16 13:34:21.733 5 S5 2009-09-16 13:32:40.703 6 S6 2011-01-16 13:32:23.703 7 S7 2011-02-16 13:32:23.703 8 S8 2011-02-16 13:32:23.703 9 S9 2011-03-16 13:32:23.703 10 S10 2011-07-16 13:32:23.703 -------------------------------------------------- Below query gives me the count of sales for different months on yearly basis. SELECT DISTINCT MonthCount = (SELECT COUNT(DATEPART("mm",LastUpdated)) FROM Sales WHERE DATEPART("yy",LastUpdated) = DATEPART("yy",p.LastUpdated) AND DATEPART("mm",LastUpdated) = DATEPART("mm",p.LastUpdated)) , "Month" = DATENAME(Month,LastUpdated),"Year"=DATEPART("yy",p.LastUpdated) FROM Sales p ORDER BY DATEPART("yy",p.LastUpdated) Here is my desired result: MonthCount Month Year ----------------------------- 1 July 2009 1 September 2009 3 August 2009 1 January 2011 1 July 2011 1 March 2011 2 February 2011

Hi , Below is the data of the 'Transaction' table: Data TransactionTime ---------------------------------------- A11 2009-07-09 16:21:15.173 A22 2009-07-09 16:50:15.000 A33 2009-07-09 17:21:15.000 A44 2009-07-02 16:21:15.000 A55 2009-07-20 16:51:15.000 A66 2009-07-10 16:21:15.000 A77 2009-07-09 18:21:15.000 Now if you need to get the count of every thursday(5th day) between (4 to 5 pm) here is the query: select count(*) from Transaction where datepart(dw,TransactionTime) = 5 and datepart(Hh,TransactionTime) + datepart(Mm,TransactionTime) * .017 between 16 and 17 Note: "datepart(Mm,TransactionTime) * .017 " will convert minutes into hours so that you can exactly get the data between 16:00 to 17:00 (hh:mm). group by convert(varchar, TransactionTime, 103)

Hi Ajit, Instead of using CASE you could build dynamic query and then execute the query. Here is the Modified SP: Option 1: CREATE PROCEDURE SP_Inventory @SortParam1 VARCHAR(50), @SortParam2 VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN EXEC ('SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY '+ @SortParam1 + ',' + @SortParam2 + ' ' + @SortOrder) Option 2: (You can send the column names in single parameter using comma seperated values for eg @SortParam = 'ItemName, ItemPrice') CREATE PROCEDURE SP_Inventory @SortParams VARCHAR(100), @SortOrder VARCHAR(5) AS BEGIN EXEC ('SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY '+ @SortParams + ' ' + @SortOrder)