In many cases, people who have these types of Pivots also eventually want a "Total" column. The ancient "Black Art" of CROSSTABs easily allows for such a thing.

SELECT PointID

,X = SUM(CASE WHEN Axis = 'X' THEN Value ELSE '' END)

,Y = SUM(CASE WHEN Axis = 'Y' THEN Value ELSE '' END)

,Total = SUM(Value)

FROM cteTestData

GROUP BY PointID

;

Result Set:

PointID X Y Total

------- ----------- ----------- -----------

P1 10 20 30

P2 30 40 70

CROSSTABs also make it pretty easy to do other things. For example...

SELECT PointID = CASE WHEN GROUPING(PointID) = 0 THEN PointID ELSE 'Total' END

,X = SUM(CASE WHEN Axis = 'X' THEN Value ELSE '' END)

,Y = SUM(CASE WHEN Axis = 'Y' THEN Value ELSE '' END)

,Total = SUM(Value)

FROM cteTestData

GROUP BY PointID WITH ROLLUP

;

Result Set:

PointID X Y Total

------- ----------- ----------- -----------

P1 10 20 30

P2 30 40 70

Total 40 60 100

The following article also explains how to do multiple column types in Pivots and CROSSTABs, explains the performance advantages of "pre-aggregation", along with a demonstration that pre-aggregatedCROSSTABs are frequently almost twice as fast as pre-aggregated Pivots. It can really make a difference in being able to meet and beat reporting SLAs. The second link demonstrates the simple power of dynamic CROSSTABs for things like perpetual sliding window reports with no code change based on GETDATE() alone.

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

To be honest, I don't ever use Pivot. I always use CROSSTABs, instead, for all the reasons previously stated and for their ease in readability.