Pivot Unpivot in SQL Server
Hello ,
Current Format of the Server table shows the data structure it’s in currently. Somehow, someway it has to be pivoted to get it in the format I need where time stamp columns transform to the rows instead. The format I need is shown in Desired Format sheet. Whats the code in SQL Server that does that.
Thanks
pivotDesired.xlsx
Current Format of the Server table shows the data structure it’s in currently. Somehow, someway it has to be pivoted to get it in the format I need where time stamp columns transform to the rows instead. The format I need is shown in Desired Format sheet. Whats the code in SQL Server that does that.
Thanks
pivotDesired.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great work guyx, let me know if there are shorter tsql code? But this looks good to me :)
If there is another way, let me know I will open another question for that.
If there is another way, let me know I will open another question for that.
ASKER
Thank You :)
ASKER
Hello Icohan,
When I just ran this:
SELECT Year, --(4)
[1] AS [1st Qtr], --(3)
[2] AS [2nd Qtr],
[3] AS [3rd Qtr],
[4] AS [4th Qtr],
[1]+[2]+[3]+[4] AS Total --(5)
FROM (SELECT Year, Quarter,Amount FROM Atest_table) AS src --(1)
PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt --(2)
ORDER BY Year
Then I got this error:
Msg 325, Level 15, State 1, Line 8
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
How can that be resolved? I am a admin…
When I just ran this:
SELECT Year, --(4)
[1] AS [1st Qtr], --(3)
[2] AS [2nd Qtr],
[3] AS [3rd Qtr],
[4] AS [4th Qtr],
[1]+[2]+[3]+[4] AS Total --(5)
FROM (SELECT Year, Quarter,Amount FROM Atest_table) AS src --(1)
PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt --(2)
ORDER BY Year
Then I got this error:
Msg 325, Level 15, State 1, Line 8
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
How can that be resolved? I am a admin…
ASKER
please assist...
ASKER
tHe last code is the only shortest way to do that? I am still learning SQL so :(