SET NOCOUNT ON

CREATE TABLE #EDIInvoices (HospitalName VARCHAR(20), Purchaser VARCHAR(20), Sales money)
INSERT INTO #EDIInvoices (HospitalName, Purchaser, Sales)
SELECT 'Guy''s', 'BUPA', 10001 UNION ALL
SELECT 'Guy''s', 'Microsoft', 10002 UNION ALL
SELECT 'Guy''s', 'NIH', 10003 UNION ALL
SELECT 'Tommy''s', 'BUPA', 20001 UNION ALL
SELECT 'Tommy''s', 'Microsoft', 20002 UNION ALL
SELECT 'Tommy''s', 'NIH', 20003 UNION ALL
SELECT 'Tommy''s', 'Cornhill', 20004 UNION ALL
SELECT 'Raigmore', 'BUPA', 30001 UNION ALL
SELECT 'Raigmore', 'Microsoft', 30002 UNION ALL
SELECT 'Raigmore', 'NIH', 30003 UNION ALL
SELECT 'Raigmore', 'Cornhill', 30004 UNION ALL
SELECT 'Raigmore', 'HPA', 30005 UNION ALL
SELECT 'Raigmore', 'RaigmoreOnly', 30006 UNION ALL
SELECT 'Yorkhill', 'BUPA', 40001 UNION ALL
SELECT 'Yorkhill', 'Microsoft', 40002 UNION ALL
SELECT 'Yorkhill', 'Cornhill', 40004 UNION ALL
SELECT 'Yorkhill', 'HPA', 40005 UNION ALL
SELECT 'Yorkhill', 'YorkhillOnly', 40007 

--SELECT * FROM #EDIInvoices -- Sanity check

-- PIVOT the results
DECLARE @SQLstr VARCHAR(5000)
SET @SQLstr = ''
SELECT @SQLstr = @SQLstr + CHAR(10) + '   MAX(CASE Purchaser WHEN ''' + Purchaser + ''' THEN Sales ELSE 0 END) AS [' + Purchaser + '], '
FROM (SELECT Purchaser FROM #EDIInvoices GROUP BY Purchaser) d

SET @SQLstr = 'SELECT HospitalName, ' + REVERSE(STUFF(REVERSE(@SQLstr),1,2,'')) + ' ' + CHAR(10) + 'INTO ##EDIInvoicesPivot FROM #EDIInvoices GROUP BY HospitalName'
PRINT @SQLstr -- sanity check

EXECUTE (@SQLstr)

SELECT * FROM ##EDIInvoicesPivot

DROP TABLE #EDIInvoices
DROP TABLE ##EDIInvoicesPivot