how to edit in pivot table and update the orginal table

  • i have a problem

    i must to show the result in pivot table evry month (employee shift)

    and the user say that he want to update in multy listBox but from the pivot table

    is this possible update the orginal table from #temp PIVOT table

    like convert back Pivot Table code to stored procedure

    any suggestion please !:hehe:

    tnx

  • well basicly what you would have to do is unpivot the data. update the source table with the normalized data.

    example from BOL

    CREATE TABLE #pvt (VendorID int, Emp1 int, Emp2 int,

    Emp3 int, Emp4 int, Emp5 int)

    GO

    INSERT INTO #pvt VALUES (1,4,3,5,4,4)

    INSERT INTO #pvt VALUES (2,4,1,5,5,5)

    INSERT INTO #pvt VALUES (3,4,3,5,4,4)

    INSERT INTO #pvt VALUES (4,4,2,5,5,4)

    INSERT INTO #pvt VALUES (5,5,1,5,5,5)

    GO

    --Unpivot the table.

    SELECT VendorID, Employee, Orders

    FROM

    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

    FROM #pvt) p

    UNPIVOT

    (Orders FOR Employee IN

    (Emp1, Emp2, Emp3, Emp4, Emp5)

    )AS #unpvt

    GO

    Hope it helps 🙂

    regards kgunnarsson

    kgunnarsson
    Mcitp Database Developer.

  • Well... it may be possible.

    If u can correctly identify the row in the original table which corresponds to the row&column in the privot table.

    "Keep Trying"

  • well. you use the unpivot temp table to join to the source table on primary key columns. then you get your data.

    if you are changing values in the pivoit table you must exclude the primary key values from being modified, so looking up the corresponding rows should not be a problem. or ?

    kgunnarsson
    Mcitp Database Developer.

  • is this code for my pivot table can Unpivot

    tnx for eny help

    DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)

    DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP

    @BaseDate SMALLDATETIME,

    @NumDays TINYINT

    SELECT @WantedDate = '20080301', -- User supplied parameter value

    @BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),

    @NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

    IF @Numdays > 28

    BEGIN

    SELECT p.ID,

    p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],

    p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],

    p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]

    FROM (

    SELECT ID,

    DATEPART(DAY, Date) AS theDay,

    ShiftID

    FROM v_Employee

    WHERE Date >= @BaseDate

    AND Date < DATEADD(MONTH, 1, @BaseDate)

    ) AS y

    PIVOT (

    min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],

    [12], [13], [14], [15], [16], [17], [18], [19], [20], [21],

    [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])

    ) AS p

    END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply