January 17, 2008 at 1:23 pm
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
January 18, 2008 at 3:22 am
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.
January 18, 2008 at 4:11 am
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"
January 18, 2008 at 4:23 am
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.
January 19, 2008 at 8:13 am
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