CREATE TABLE #prePivot (
	[LiquidityFlag] VARCHAR(20)
	, [lastMkt] CHAR(4)
	, [lastQty] INT
	)

CREATE TABLE #results (
	[Destination] CHAR(4)
	, [ADDED] INT
	, [REMOVED] INT
	, [ROUTED] INT
	)

INSERT INTO #prePivot
      SELECT 'ADDED', 'NSDQ', 100
UNION SELECT 'REMOVED', 'ARCA', 500
UNION SELECT 'REMOVED', 'NSDQ', 100
UNION SELECT 'REMOVED', 'NSDQ', 100
UNION SELECT 'ROUTED', 'ARCA', 500

-- Populate destination data
INSERT INTO #results (
	[liquidityFlag]
	)
SELECT DISTINCT
	[lastMkt]
FROM
	#prePivot

-- populate ADDED
UPDATE #results
SET
	[ADDED] = a.[lastQty]
FROM
	(
	SELECT
		[lastMkt] AS lastMkt
		, SUM([lastQty]) AS lastQty
	FROM
		#prePivot
	WHERE
		[liquidityFlag] = 'ADDED'
	GROUP BY
		[lastMkt]
	)
	a
WHERE
	a.[lastMkt] = #results.[Destination]

SELECT LastMkt AS Destination,
SUM(CASE WHEN LiquidityFlag='ADDED' THEN LastQty ELSE 0 END) AS ADDED,
SUM(CASE WHEN LiquidityFlag='REMOVED' THEN LastQty ELSE 0 END) AS REMOVED,
SUM(CASE WHEN LiquidityFlag='ROUTED' THEN LastQty ELSE 0 END) AS ROUTED
FROM Table
GROUP BY LastMkt