Create a Temp Table, inserting specific values to avoid duplicating rows

Can you clarify your spec with some example, please? it may be easier for us to help you.

bpnchan,
Thank you for responding.  The following query is returning the row of details data for each employee but since the employee may have a number of assets assigned, this returns the details and duplicates the lines because of the different asset managers.

SELECT  Distinct   CEmpDetails.EmployeeID, 
                   CEmpDetails.FunctionalMgrID, 
                   CEmpDetails.Job, 
                   CEmpDetails.WBS, 
                   CEmpDetails.LastworkDate, 
                   CEmpDetails.EmployeeName, 
                   CEmpDetails.FunctionalMgrName, 
                   CEmpDetails.FunctionalMgrEmail, 
                   CEmpDetails.TalentMgrName, 
                   CEmpDetails.TalentMgrEmail, 
                   CEmpDetails.Library, 
                   AMgr.AssetMgrName, 
                   AMgr.AssetMgrEmail
FROM         CEmpDetails Left Outer Join
             AMgr ON CEmpDetails.EmployeeID = AMgr.EmployeeID
WHERE     (CEmpDetails.EmailSent = 0) And EmployeeName is not NULL

Select allOpen in new window

Hi,
   can you please attach the table structures as well. so it will be helpful for us.

SELECT  CEmpDetails.EmployeeID,
            CEmpDetails.FunctionalMgrID,
            CEmpDetails.Job,
            CEmpDetails.WBS,
            CEmpDetails.LastworkDate,
            CEmpDetails.EmployeeName,
            CEmpDetails.FunctionalMgrName,
            CEmpDetails.FunctionalMgrEmail,
            CEmpDetails.TalentMgrName,
            CEmpDetails.TalentMgrEmail,
            CEmpDetails.Library,
            AMgr.AssetMgrName,
            AMgr.AssetMgrEmail
FROM         CEmpDetails Left Outer Join
             AMgr ON CEmpDetails.EmployeeID = AMgr.EmployeeID
WHERE     (CEmpDetails.EmailSent = 0) And EmployeeName is not NULL
Group By  CEmpDetails.EmployeeID,
            CEmpDetails.FunctionalMgrID,
            CEmpDetails.Job,
            CEmpDetails.WBS,
            CEmpDetails.LastworkDate,
            CEmpDetails.EmployeeName,
            CEmpDetails.FunctionalMgrName,
            CEmpDetails.FunctionalMgrEmail,
            CEmpDetails.TalentMgrName,
            CEmpDetails.TalentMgrEmail,
            CEmpDetails.Library,
            AMgr.AssetMgrName,
            AMgr.AssetMgrEmail

mimram18, thank you for responding.  I still get duplicate rows with the query because there are multiple asset manager for each employee.

Is there a way that I can
selecting all of the details, then select the asset managers and add them to individual columns in as temp table?  There is only a possiblity of having 7 asset managers.

Theis whould give me one row of data.

Can you plz give me some sample data ? will help to understand the relationship between tables.

I have attached a diagram of these test tables.  I just recently added the AssetMgr table hoping it would help me.  Can delete it if necessary.

I was looking to dynamically add columns sort of like the following example... only, I would loop through the EmployeeAssets table and add a column for each Asset Manager and Asset Manager Email.

https://www.experts-exchange.com/questions/26476687/Dynamically-add-columns-to-temp-table-based-on-data-in-source-table.html
TableDiagram.doc

I'm going to try working through the logic of one of the samples.  Just found another...  Just have to get a couple hours of sleep first.

DECLARE @VisitingHoursStartTime AS DATETIME --Cursor Local Variables
    DECLARE @VisitingHoursEndTime AS DATETIME

    SET @VisitingHoursStartTime = '09:00' -- morning 9 am
    SET @VisitingHoursEndTime = '17:00' -- evening 6 pm

    CREATE TABLE #Temp(Id NVARCHAR(50)) -- Creating Temp Table

    -- Loop to add columns to temp table
    WHILE (@VisitingHoursStartTime <= @VisitingHoursEndTime)
    BEGIN
        DECLARE @DynamicSQL VARCHAR(500)

        IF NOT EXISTS ( SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('[dbo].[#Temp]') AND NAME = '@VisitingHoursStartTime' )
        BEGIN
            SET @DynamicSQL = 'ALTER TABLE #Temp ADD ['+ CONVERT(VARCHAR(5),@VisitingHoursStartTime,108) +'] NVARCHAR(100) NULL'
            EXECUTE (@DynamicSQL)
        END

        SET @VisitingHoursStartTime = DateADD(MI,15,@VisitingHoursStartTime)
        IF @VisitingHoursStartTime = @VisitingHoursEndTime
            BREAK;
    END

    SELECT * FROM #Temp

    IF EXISTS(SELECT * FROM sysobjects WHERE NAME LIKE '%#Temp%')
        DROP TABLE #Temp
END

Select allOpen in new window

you could also do the query directly to get distinct values:
https://www.experts-exchange.com/A_3203.html

angellll,
Does it matter that the tables are used as utilities.... all previous data is deleted first then new data is automatically added.  The primary keys change.   Don't know if this matters??

it does not matter, because your "DISTINCT" is not using the generated key values, but must rely on the column data you actually want to insert. ... if I got the question right ...

Users accepted my solution.