-- This is a simple pivot where the table contains a single value per entity and the requirement is for these values in a single row.

Create table #a (Company varchar(20), value varchar(20))
insert #a select 'Company01', '100'
insert #a select 'Company02', '200'
insert #a select 'Company03', '300'


/*
for this we generate the following query

select	Company01=(select value from #a where Company = 'Company01'),
	Company02=(select value from #a where Company = 'Company02'),
	Company03=(select value from #a where Company = 'Company03')
*/
declare @s varchar(8000)

declare @Company varchar(20), @MaxCompany varchar(20)
select @Company = '', @MaxCompany = max(Company) from #a
while @Company < @MaxCompany
begin
	select @Company = min(Company) from #a where Company > @Company
	select @s = coalesce(@s + ',', 'select ') + @Company + '=(select value from #a where Company = ''' + @Company + ''')'
end

select @s
exec (@s)

drop table #a

-- Similarly but with a date per value - requirement one date per row
Create table #a (Company varchar(20), dte datetime, value varchar(20))
insert #a select 'Company01', '20030101', '100'
insert #a select 'Company02', '20030101', '200'
insert #a select 'Company03', '20030101', '300'
insert #a select 'Company01', '20030102', '400'
insert #a select 'Company02', '20030102', '500'
insert #a select 'Company03', '20030102', '600'
insert #a select 'Company01', '20030103', '700'
insert #a select 'Company02', '20030103', '800'
insert #a select 'Company03', '20030103', '900'

/*
for this we generate the following query
select	date = t.dte, 
	Company01=(select value from #a t1 where t1.Company = 'Company01' and t1.dte = t.dte),
	Company02=(select value from #a t1 where t1.Company = 'Company02' and t1.dte = t.dte),
	Company03=(select value from #a t1 where t1.Company = 'Company03' and t1.dte = t.dte) 
from #a t 
group by t.dte 
order by t.dte
*/

declare @s varchar(8000)

declare @Company varchar(20), @MaxCompany varchar(20)
select @Company = '', @MaxCompany = max(Company) from #a
while @Company < @MaxCompany
begin
	select @Company = min(Company) from #a where Company > @Company
	select @s = coalesce(@s + ',', '') + @Company + '=(select value from #a t1 where t1.Company = ''' + @Company + ''' and t1.dte = t.dte)'
end
select @s = 'select date = t.dte, ' + @s + ' from #a t group by t.dte order by t.dte' 
select @s
exec (@s)

drop table #a

-- Large tables
Of course the above will fail when the string to execute becomes larger than 8000 characters.
To get round this we can use the string concatenation feature of the exec statement.
The exec statement takes a text datatype and so can accept concatenated strings greater than 8000 characters.
exec(@s1 + @s2 + @s3)
For this I build up the string to be executed in a text column of a temp table then split it up into strings for the exec.

-- Create and populate the source table
Create table #a (Company varchar(20), dte datetime, value varchar(20))
declare @i int, @j int
select @i = 0
while @i <= 1000
begin
	select @i = @i + 1
	select @j = 0
	while @j < 10
	begin
		select @j = @j + 1
		insert #a select 'company' + right('000'+convert(varchar(4),@i),4), dateadd(dd,@j,'20021231'), 1000*rand()
	end
end

-- Create the select statement in a text field
create table #text (s text)
insert #text select 'select date = t.dte,'

declare @ptr binary(16)
	select @ptr = textptr(s) from #text
	
declare @Company varchar(20), @MaxCompany varchar(20)
declare @s varchar(8000)
select @Company = '', @MaxCompany = max(Company) from #a
while @Company < @MaxCompany
begin
	select @Company = min(Company) from #a where Company > @Company
	select @s = @Company + '=(select value from #a t1 where t1.Company = ''' + @Company + ''' and t1.dte = t.dte)'
	updatetext #text.s @ptr null null @s
end

-- Now create the execute statement
declare @exec varchar(8000)
declare	@execentry int
declare @execentryvar varchar(20)
declare @len int, @maxlen int
select @len = 1, @maxlen = datalength(s) from #text
select @exec = '', @execentry = 0
while @len < @maxlen
begin
	select @execentry = @execentry + 1
	select @execentryvar = '@s' + convert(varchar(10),@execentry)
	select @exec = 'declare ' + @execentryvar + ' varchar(8000) select ' + @execentryvar + '=substring(s,+ convertvarchar(20),@len) + ',8000) from #text '
	select @len = @len + 8000	
end
select @exec
drop table #text