How to write query?

Question

Hi Example: EmpIDEmpName Place 1 Aparna Hyd 2 Aparna Bangalore 3 Aparna Delhi How to write query for Aparna belongs to three places i have to display in one line like Aparna hydbangloredelhi how to write query for that plz help me

John Penn · Answer

FOR XML PATH is normally used to generate a string of xml elements. Try that query with FOR XML PATH('Foo') and you'll see what I mean. In our case we're using it to concatentate the string for us. By passing it an empty string we're basically saying construct an xml string from our data but don't include any element names.

karthik parcha · Answer

hi i have to generat ein report iam writing inline query in .RDL file SELECT e.EmpName + ' ' + p.ProjectName AS EXP_ASMT FROM SF_ProjectEmp pe INNER JOIN SF_Project p ON pe.ProjId = p.ProjId INNER JOIN EmpUser eu ON pe.UserUniqueId = eu.UserUniqueId INNER JOIN Employee e ON eu.EmpId = e.EmpId WHERE DATEDIFF(DAY Getdate() CanvassEndDate) = 7 AND eu.RoleId IN (31) Group by e.EmpNamep.ProjectName ActualOutput: EXP_ASMT : Karthik Banking Karthik Healthcare Karthik Sales SO i have to display like EXP_ASMT : karthik BankingHealthcareSales output should be like this plz help me zoran

karthik parcha · Answer

hi thanq nice explanation

Jiteendra Sampathirao · Answer

Hi karthik Nice thread. Thank you Zoran Horvat and John Penn

karthik parcha · Answer

hi can u explain your query and why you use xml path i dint understand plz SELECT EmpName  SUBSTRING(( SELECT ( '' + Place ) FROM Karthik2 k2 WHERE k1.EmpName = k2.EmpName ORDER BY k1.EmpName  k2.EmpName FOR XML PATH('') ) 2 1000) FROM Karthik2 k1 GROUP BY k1.EmpName

John Penn · Answer

I used a cursor because it's a reliable way to iterate through a table one row at a time and do the kind of processing that is needed to generate the resultset you're looking for. One drawback with cursors is that they're very resource-intensive so they should be used only when necessary. I did some googling and found another method of getting this same output without using a cursor. I adapted their code to mine and this works too: SELECT EmpName  SUBSTRING(( SELECT ( '' + Place ) FROM Karthik2 k2 WHERE k1.EmpName = k2.EmpName ORDER BY k1.EmpName  k2.EmpName FOR XML PATH('') ) 2 1000) FROM Karthik2 k1 GROUP BY k1.EmpName There didn't seem to be a need to store the resultset permanently which is why I used a temporary table. Once the procedure returns the temp table is dropped.

karthik parcha · Answer

hi thanq for replying can u expalin your storedprocedure so that i can easily understand why we are using temp tables and why we are using cursors plz plz plz can u explain for me plz what is the advantages of using cursors and temp tables plz explain

karthik parcha · Answer

okay zoran thanq for helping i checked your query as correct answer

John Penn · Answer

Given this table definition: CREATE TABLE [dbo].[Karthik2]( [EmpID] [int] IDENTITY(11) NOT NULL [EmpName] [varchar](50) NOT NULL [Place] [varchar](50) NOT NULL ) ON [PRIMARY] Here's a stored procedure that uses a cursor to loop through the records and return the comma delimited strings you're after: CREATE PROCEDURE [dbo].[GetPlaces] AS CREATE TABLE #tempPlaces ( EmpName VARCHAR(50) Place VARCHAR(50) ) DECLARE @currentName VARCHAR(50) @currentPlace VARCHAR(50) DECLARE places_cursor CURSOR FOR SELECT EmpName Place FROM dbo.Karthik2 OPEN places_cursor FETCH NEXT FROM places_cursor INTO @currentName @currentPlace WHILE @@FETCH_STATUS = 0 BEGIN IF EXISTS (SELECT 1 FROM #tempPlaces WHERE EmpName = @currentName) UPDATE #tempPlaces SET Place = Place + '' + @currentPlace WHERE EmpName = @currentName ELSE INSERT INTO #tempPlaces ( EmpName Place ) VALUES ( @currentName @currentPlace ) FETCH NEXT FROM places_cursor INTO @currentName @currentPlace END CLOSE places_cursor DEALLOCATE places_cursor SELECT * FROM #tempPlaces GO

Zoran Horvat · Answer

I believe that there is no single-query solution to the problem. Actually RDL is not meant to be a programming tool but rather reporting tool. Here you're changing the format of the data. To solve the problem you can create stored procedure which concatenates values for given EmpName. I don't have SSRS available here so I can't help you too much with it. Zoran

karthik parcha · Answer

hi thanq but i have to show in report i dont want to use any ado.net concepts and stringbuilder every thing i have to write in query your code is good but i cant write liststringbulder in SSRS i.e rdl file how we write your logic in queeery plzzzzzzzzzzzzzzzzzzzzzzzz

Zoran Horvat · Answer

Ok that makes a bit more work. First change query into this: SELECT e.EmpName AS EmpName p.ProjectName AS ProjectName FROM SF_ProjectEmp pe INNER JOIN SF_Project p ON pe.ProjId = p.ProjId INNER JOIN EmpUser eu ON pe.UserUniqueId = eu.UserUniqueId INNER JOIN Employee e ON eu.EmpId = e.EmpId WHERE DATEDIFF(DAY Getdate() CanvassEndDate) = 7 AND eu.RoleId IN (31) Group by e.EmpNamep.ProjectName ORER BY e.EmptName p.ProjectName This will ensure that EmpName is the same in all consecutive rows returned until all ProjectName values for given EmpName are exhausted. Once all of them pass next EmpName value will appear with all its ProjectName values. Code which uses this query (in its principal form) is this: SqlCommand cmd = new (... conn); List list = new List ; StringBuilder sb = new StringBuilder() string lastEmpName = null; // Change in EmpName triggers creation of new entry in the List using (SqlDataReader reader = cmd.ExecuteReader()) { int empNameIndex = reader.GetOrdinal(EmpName); int projNameIndex = reader.GetOrdinal(ProjectName); string empName = null; string projName = null; while (reader.Read()) { empName = reader.GetString(empNameIndex); projName = reader.GetString(projNameIndex); if (empName != lastEmpName) { // New emplyer name has occurred on input if (sb.Length > 0) list.Add(sb.ToString()); // Add previous list to output sb.Length = 0; // Clear the list for new employer name first = true; sb.Append(empName); } // Now append project name to current list if (first) sb.Append( ); else sb.Append( ); first = false; sb.Append(projName); } // Now add the last line to the output if (sb.Length > 0) list.Add(sb.ToString()); } // Now list contains all lines grouped by EmpName column value... Try this code for errors I haven't tried it. Zoran

karthik parcha · Answer

Hi thanq for replyng me actually their are so many employees with repeated like as i mentioned in my example if same employee with differnet projects so i have to displayed not to repeat employee name example karthik bankinghealthsales zoran Architectbankinghealth in this karthik and zoran are repeated three times but u want to display in single line plz canu write query for me i tried but i unable to write the query for that plz help me

Zoran Horvat · Answer

Do you always need output for only one EmpName? Zoran

karthik parcha · Answer

Zoran did u get know the query for that

Zoran Horvat · Answer

SqlCommand cmd = new (SELECT Place FROM Emp WHERE EmpName='Aparna' conn); StringBuilder sb = new StringBuilder() sb.Append(Aparna); bool first = true; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (first) sb.Append( ); else sb.Append( ); first = false; sb.Append(reader.GetString(0)); } } // At this point sb.ToString() contains the requested list. Zoran