create table Speeds
(
  RoadID int not null
  ,BinID smallint not null
  ,Speed tinyint not null
  ,primary key(RoadID asc, BinID asc)
)

select RoadID, BinID, Speed 
from dbo.ComplicatedTVF(@someArgs) 
order by RoadID, BinID

-- using PIVOT:
select RoadID,[0],[1],...[999]
from (select RoadID, Speed, BinID from dbo.ComplicatedTVF()) a
pivot (min(Speed) for BinID in ([0],[1],...[999])) b

-- using CASE:
select
  RoadID
  ,min(CASE BinID when 0 then Speed else NULL end) AS Bin0
  ,min(CASE BinID when 1 then Speed else NULL end) AS Bin1
  ...
  ,min(CASE BinID when 999 then Speed else NULL end) AS Bin999
  from dbo.ComplicatedTVF()
group BY RoadID

Msg 8632, Level 17, State 2, Line 61
Internal error: An expression services limit has been reached. 
Please look for potentially complex expressions in your query, and try to simplify them

quote:

---

Originally posted by jsmith8858

Can you generate this CSV file with a custom app, say one written in .NET? It would be only a few simple lines of code to do the work there, I'd be happy to help if you can provide your specific requirements. It will be not only easier and much less code, but also much, much faster and more flexible.

---

quote:

---

Originally posted by jsmith8858
However, I still have to ask: What, then, will the customer do with this file? They won't be able to make sense of it viewing it in Notepad, and they won't be able to import that many columns into a table or Excel file...what is the final goal here? This reminds me of the client who insisted that their primary reporting need was a report that would be 300,000 printed pages because they "always needed to see everything."

---

CREATE PROCEDURE uspSalesOfficeDepartmentPivot
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
AS

SET NOCOUNT ON

CREATE TABLE	#Aggregates
		(
			RowText VARCHAR(50),
			ColumnText VARCHAR(50),
			CellData INT
		)

INSERT		#Aggregates
		(
			RowText,
			ColumnText,
			CellData
		)
SELECT		Sales.Office,
		Sales.Department,
		SUM(Invoices.Amount)
FROM		Sales
INNER JOIN	Invoices ON Sales.OfficeID = Invoices.OfficeID
WHERE		Invoices.OrderDate BETWEEN @FromDate AND @ToDate
GROUP BY	Sales.Office,
		Sales.Department
HAVING		SUM(Invoices.Amount) <> 0

CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)

CREATE TABLE	#Columns
		(
			ColumnIndex INT IDENTITY (0, 1),
			ColumnText VARCHAR(50)
		)

INSERT		#Columns
		(
			ColumnText
		)
SELECT DISTINCT	ColumnText
FROM		#Aggregates WITH (NOLOCK, INDEX(IX_Aggregates))
ORDER BY	ColumnText

CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)

CREATE TABLE	#Rows
		(
			RowText VARCHAR(50)
		)

INSERT		#Rows
		(
			RowText
		)

SELECT DISTINCT	RowText
FROM		#Aggregates WITH (NOLOCK, INDEX(IX_Aggregates))

CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)

DECLARE	@ColumnIndex INT,
	@MaxColumnIndex INT,
	@ColumnText VARCHAR(50),
	@SQL VARCHAR(1000)

SELECT	@ColumnIndex = 0,
	@MaxColumnIndex = MAX(ColumnIndex)
FROM	#Columns

WHILE @ColumnIndex <= @MaxColumnIndex
	BEGIN
		SELECT	@ColumnText = ColumnText
		FROM	#Columns
		WHERE	ColumnIndex = @ColumnIndex

		SELECT	@SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT NULL DEFAULT 0'
		EXEC	(@SQL)
 
		SET	@SQL = 'UPDATE		r
				SET		r.' + QUOTENAME(@ColumnText) + ' = a.CellData
				FROM		#Rows AS r
				INNER JOIN	#Aggregates AS a WITH (NOLOCK, INDEX(IX_Aggregates)) ON a.RowText = r.RowText
				INNER JOIN	#Columns AS c WITH (NOLOCK, INDEX(IX_Columns)) ON c.ColumnText = a.ColumnText
				WHERE		c.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
		EXEC	(@SQL)
 
		SET	@ColumnIndex = @ColumnIndex + 1 
	END

DROP TABLE	#Columns,
		#Aggregates

SELECT		*
FROM		#Rows
ORDER BY	RowText

DROP TABLE	#Rows