You know, sometimes the obvious is just too easy to see. Thanks.

I have been concentrating on getting rid of the WHILE LOOP.

When I first looked at the stored procedure...

Thanks Nevyn. That help me figure out what I did wrong.

Learned something new today. Didn't know what a "six degrees of kevin bacon" was. Interesting readying.

Anyway, the...

Something to help get you started. One of many ways including numbers/tally tables.

--DROP TABLE #Agent

-- create a temp table

CREATE TABLE #Agent ([state] varchar(50)

,AgentName varchar(50)

,Agenttype varchar(50)

,Timecreated datetime

,amount int

)

-- insert data...

By the way, the example sent using Cross Tabs will produce incorrect results. You will need to get a count of invoice id's or invoice numbers, not fileid's.

Try this, not too hard to understand. There are many sites with examples of dynamic pivot tables.

/*

put distinct invoice numbers into a comma separated variable

for use in the pivot...

Read this article

http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

OK, I'll give it a shot and answer your original question. This should work to build upon your other queries by use of temp tables, JOINs (UNION ALL), or...

This article, first of two by Jeff Moden, should help you.

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