Pivoting Data:
Pivoting is the process of converting rows of data into columns, effectively changing the orientation of your data. It's useful when you have data stored in a "long" format (with multiple rows) and you want to present it in a "wide" format (with columns). You often use an aggregate function like SUM, COUNT, AVG, etc., to aggregate values for each column.
WITH PivotData AS
(
SELECT
custid , -- grouping column
shipperid, -- spreading column
freight -- aggregation column
FROM Sales.Orders
)
SELECT custid, , ,
FROM PivotData
PIVOT (SUM(freight) FOR shipperid in (, , )) as P
Unpivoting Data:
Unpivoting is the reverse operation of pivoting. It transforms columns into rows. This is useful when you have data in a "wide" format (with columns) and you want to convert it into a "long" format (with rows).
Above pivot result save in Temp table:
WITH PivotData AS
(
SELECT
custid , -- grouping column
shipperid, -- spreading column
freight -- aggregation column
FROM Sales.Orders
)
SELECT custid, , ,
INTO #FreightTotals
FROM PivotData
PIVOT (SUM(freight) FOR shipperid in (, , )) as PSELECT
custid,
shipperid,
freight
FROM #FreightTotals
UNPIVOT (
freight FOR shipperid IN (, , )
) AS U;
If you have a dataset where the shiperid values change over time, and you want to pivot the data without using a dynamic pivot method, you can consider using SQL techniques to achieve this.
ALTER PROCEDURE TestSP
AS
BEGIN
-- Create a table to hold the distinct HeadName values
CREATE TABLE #DistinctHeadNames (
RowNum INT IDENTITY(1,1),
HeadName VARCHAR(50)
)
-- Insert the distinct HeadName values into the table
INSERT INTO #DistinctHeadNames (HeadName)
SELECT DISTINCT O.shipperid
FROM Sales.Orders O
DECLARE @totalHead INT = 0
DECLARE @CurHead INT = 1
DECLARE @ColName VARCHAR(100) = ''
DECLARE @SQL VARCHAR(MAX) = 'ALTER TABLE #TempTable'
DECLARE @SQL1 VARCHAR(MAX) = ''
-- Create a table to hold the dynamic columns
CREATE TABLE #TempTable
(
custid INT
)
--Inser data in #TempTable
table
INSERT #TempTable (CustId)
SELECT DISTINCT O.custid FROM Sales.Orders O
--Get all distinct shipperid count
SELECT @totalHead = COUNT(DISTINCT HeadName) FROM #DistinctHeadNames
SELECT O.custid,O.shipperid,SUM(O.freight) Totafreight
INTO #Totafreight
FROM Sales.Orders O
GROUP BY O.custid,O.shipperid</em>
-- Loop through the distinct HeadName values
SET @CurHead = 1
WHILE @CurHead <= @totalHead AND @totalHead>0
BEGIN
SELECT @ColName = HeadName FROM #DistinctHeadNames WHERE RowNum = @CurHead
IF @CurHead > 1
SET @SQL = @SQL + ', '
ELSE
SET @SQL = @SQL + ' ADD '
SET @SQL = @SQL + QUOTENAME(@ColName) + ' VARCHAR(10) NOT NULL DEFAULT ''0'''
SET @SQL1 = @SQL1+ 'UPDATE #TempTable SET '+ QUOTENAME(@ColName)+ ' = Totafreight FROM #Totafreight T WHERE T.custid = #TempTable.custid and QUOTENAME(T.shipperid) = ''' +QUOTENAME(@ColName)+ '''; '
--SET @SQL1 = @SQL1+ 'UPDATE #TempTable SET '+ QUOTENAME(@ColName)+ ' = Totafreight FROM #Totafreight T WHERE T.custid = #TempTable.custid and QUOTENAME(T.shipperid) ='''''+'; '
SET @CurHead = @CurHead + 1
END
EXEC(@SQL)
EXEC (@SQL1)
SELECT * FROM #TempTable
DROP TABLE #DistinctHeadNames
DROP TABLE #TempTable
DROP TABLE #Totafreight
END
GO
EXEC TestSPOldValue and NewValue with Field name
WITH CTE AS
(
SELECT
SD.SalaryID,
SD.EmployeeID,
lag(CAST(SD.WEFDate AS VARCHAR(20))) OVER(Partition By SD.EmployeeID ORDER BY WEFDate) as old_WEFDate,
CAST(SD.WEFDate AS VARCHAR(20)) AS WEFDate,
lag(CAST(SD.ToDate AS VARCHAR(20))) OVER(Partition By SD.EmployeeID ORDER BY WEFDate) as old_ToDate,
CAST(SD.ToDate AS VARCHAR(20)) AS ToDate,
lag(CAST(SD.GrossSalary AS VARCHAR(20))) OVER(Partition By SD.EmployeeID ORDER BY WEFDate) as old_GrossSalary,
CAST(SD.GrossSalary AS VARCHAR(20)) AS GrossSalary
FROM PAY_SalaryDetails SD
WHERE SD.EmployeeID = 17
)
SELECT
SalaryID,
EmployeeID,
Attribute,
OldValue,
NewValue
FROM CTE
CROSS APPLY (
VALUES
('WEFDate', old_WEFDate, WEFDate),
('ToDate', old_ToDate, ToDate),
('GrossSalary', old_GrossSalary, GrossSalary)
) AS FieldName(Attribute, OldValue, NewValue);
Dynamic method
DECLARE @EmployeeID INT = 17;
DECLARE @cols NVARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX) = '';
-- Use FOR XML PATH to concatenate column names instead of STRING_AGG
SELECT @cols = STUFF((
SELECT
' UNION ALL
SELECT
SalaryID,
EmployeeID,
''' + name + ''' AS FieldName,
CAST(LAG(' + name + ') OVER(PARTITION BY EmployeeID ORDER BY WEFDate) AS VARCHAR(100)) AS OldValue,
CAST(' + name + ' AS VARCHAR(100)) AS NewValue
FROM PAY_SalaryDetails
WHERE EmployeeID = ' + CAST(@EmployeeID AS VARCHAR)
FROM sys.columns
WHERE object_id = OBJECT_ID('PAY_SalaryDetails')
AND name IN ('GrossSalary') -- specify only the fields you want to track
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '');
-- Final query
SET @sql = '
WITH ChangeData AS (
' + @cols + '
)
SELECT
SalaryID,
EmployeeID,
FieldName,
OldValue,
NewValue,
CASE
WHEN OldValue IS NULL AND NewValue IS NOT NULL THEN ''New Entry''
WHEN OldValue IS NOT NULL AND NewValue IS NULL THEN ''Removed''
WHEN OldValue = NewValue THEN ''No Change''
ELSE ''Changed''
END AS ChangeFlag
FROM ChangeData;
';
-- Execute
EXEC sp_executesql @sql;
--Extra
DECLARE @ReviewRoles TABLE (
EmployeeID INT,
EmployeeName VARCHAR(100),
TravelRequestID INT,
ARWorkFlowID INT,
ReviewLevel INT,
ReviewRoleID INT,
ARStatus INT
);
;WITH
ARLevel AS (
SELECT
AR.ARWorkFlowID,
ED.EmployeeID,
ED.EmployeeName,
TR.TravelRequestID,
--AR.ReviewLevel,
CASE AR.ReviewRole
WHEN 1 THEN ED.ReportHead1
WHEN 2 THEN ED.ReportHead2
WHEN 3 THEN DeptHead.EmployeeID
WHEN 4 THEN BranchHead.EmployeeID
WHEN 5 THEN BranchAuth.BranchAuthorisedEmpID
WHEN 6 THEN AR.ReviewRoleID
WHEN 7 THEN AR.ReviewRoleID
ELSE 0
END AS ReviewRoleID
,ROW_NUMBER() OVER(Partition By TR.TravelRequestID ORDER BY ReviewLevel) NewLevel
FROM TRVL_TravelRequests TR
INNER JOIN EmployeeDetails ED ON ED.EmployeeID = TR.EmployeeID
LEFT JOIN EmployeeDetails DeptHead ON DeptHead.IsDepartmentHead = 1 AND DeptHead.DepartmentID = ED.DepartmentID
LEFT JOIN EmployeeDetails BranchHead ON BranchHead.IsBranchHead = 1 AND BranchHead.BranchID = ED.BranchID
LEFT JOIN BranchDetails BranchAuth ON BranchAuth.BranchID = ED.BranchID
INNER JOIN TRVL_GradeDesignations GD ON GD.DesignationID = ED.DesignationID
INNER JOIN TRVL_ARWorkFlows AR ON AR.GradeID = GD.GradeID
WHERE
CASE AR.ReviewRole
WHEN 1 THEN ED.ReportHead1
WHEN 2 THEN ED.ReportHead2
WHEN 3 THEN DeptHead.EmployeeID
WHEN 4 THEN BranchHead.EmployeeID
WHEN 5 THEN BranchAuth.BranchAuthorisedEmpID
WHEN 6 THEN AR.ReviewRoleID
WHEN 7 THEN AR.ReviewRoleID
ELSE 0
END IS NOT NULL
AND CASE AR.ReviewRole
WHEN 1 THEN ED.ReportHead1
WHEN 2 THEN ED.ReportHead2
WHEN 3 THEN DeptHead.EmployeeID
WHEN 4 THEN BranchHead.EmployeeID
WHEN 5 THEN BranchAuth.BranchAuthorisedEmpID
WHEN 6 THEN AR.ReviewRoleID
WHEN 7 THEN AR.ReviewRoleID
ELSE 0
END <> TR.CreateBy
),
CurrentStatus AS (
SELECT
TR_AR.TravelRequestID,
MAX(AR.NewLevel) AS MaxApprovedLevel
FROM TRVL_TravelRequestAR TR_AR
INNER JOIN ARLevel AR ON TR_AR.ARWorkFlowID = AR.ARWorkFlowID
WHERE TR_AR.ARStatus = 2 -- Approved
GROUP BY TR_AR.TravelRequestID
)
INSERT INTO @ReviewRoles (
EmployeeID,
EmployeeName,
TravelRequestID,
ARWorkFlowID,
ReviewLevel,
ReviewRoleID,
ARStatus
)
SELECT
EH.EmployeeID,
EH.EmployeeName,
EH.TravelRequestID,
ARWorkFlowID,
NewLevel,
ReviewRoleID,
1 AS ARStatus -- Pending
FROM ARLevel EH
LEFT JOIN CurrentStatus CS ON CS.TravelRequestID = EH.TravelRequestID
WHERE
(
(CS.MaxApprovedLevel IS NULL AND EH.NewLevel = 1)
OR (CS.MaxApprovedLevel IS NOT NULL AND EH.NewLevel = CS.MaxApprovedLevel + 1)
)