How to write query?

hi
thanq nice explanation

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

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

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.

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

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

Given this table definition:

CREATE TABLE [dbo].[Karthik2](
    [EmpID] [int] IDENTITY(1,1) 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

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

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 list,stringbulder in SSRS i.e rdl file
how we write your logic in queeery
plzzzzzzzzzzzzzzzzzzzzzzzz

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.EmpName,p.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

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 banking,health,sales
zoran Architect,banking,health

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

Do you always need output for only one EmpName?

Zoran

Zoran
did u get know the query for that

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.EmpName,p.ProjectName


ActualOutput:
EXP_ASMT:
Karthik Banking
Karthik Healthcare
Karthik Sales

SO i have to display like
EXP_ASMT:
karthik Banking,Healthcare,Sales
output should be like this plz help me zoran

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