Really nice to read, Jeff, and absolutely spot on.

I've had to do pivots in the last couple of weeks, with an unknown number of output (transposed) columns with unknown names, as in the following. It will be interesting to see your solution, you have a knack of coming up with "why the heck didn't I think of that?" solutions!

Cheers

ChrisM

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