Hi i need a different grid format.I dont know how to do this.

Please help....

     Date              Location                  observation

    25/6/2009        A1                               HI

   25/6/2009         A2                               Nil

   25/6/2009         A1                               Xxx

   25/6/2009         A2                               Yyy

    25/6/2009        A1                               AAA

 Here A1,A2 is not limited.It goes upto An

i need it in this format

observation

      Date               A1       A2 .......................

  25/6/2009            HI        Nil

 25/6/2009             Xxx      Yyy         

  25/6/2009           AAA                 

Use Pivoting.

Refer to:

http://www.orafaq.com/node/1871

http://sqlserveradvisor.blogspot.com/2009/03/sql-server-convert-rows-to-columns.html

Use Pivoting.

Refer to:

http://www.sqlsnippets.com/en/topic-12180.html

http://myitforum.com/cs2/blogs/jnelson/archive/2007/10/09/Turning-Rows-into-Columns-using-PIVOT.aspx

Definately you can create this..

but it is little bit tricky

1. you have to create Stored Procedure and  in stored procedure according to your requirement create a DataTable and Return tht DataTable and then bindgrid.
2. you can create DataTable at Backend(in aspx.vb/cs) also and then bind your grid with that Generated Grid view...

i will try to create Sample for you if i will get time...

till tht i can guide you here...

All The Best

Please try this...

--drop table PivotTable 

create table PivotTable  (Date varchar(10), Location varchar(2), observation varchar(20))

insert into PivotTable

select '25/6/2009', 'A1', 'HI'
union all select '25/6/2009', 'A2', 'Nil'
union all select '25/6/2009', 'A1', 'Xxx'
union all select '25/6/2009', 'A2', 'Yyy'
union all select '25/6/2009', 'A1', 'AAA'
union all select '25/6/2009', 'A3', '3AA'
union all select '25/6/2009', 'A3', '3VB'
union all select '22/6/2009', 'A4', '4AA'
union all select '22/6/2009', 'A4', '4VB'

declare @maxcount int, @counter int
set @counter = 1

declare @count_table table(count_id int identity(1,1), location varchar(2))

insert into @count_table (location)
select location from PivotTable group by location

select @maxcount = count(*) from @count_table

declare @dynamicquery nvarchar(2000)
set @dynamicquery = 'declare @finalTable table (date varchar(10), '

while @counter <= @maxcount
begin

	if @counter = @maxcount	
	begin
		select @dynamicquery = @dynamicquery + location + ' varchar(20)) ' from @count_table
		where count_id = @counter
	end
	else
	begin
		select @dynamicquery = @dynamicquery + location + ' varchar(20), ' from @count_table
		where count_id = @counter
	end
	
	set @counter = @counter + 1
end

set @counter = 1

while @counter <= @maxcount
begin

	select 	@dynamicquery = @dynamicquery + ' insert into @finalTable (date, A' + cast(@counter as varchar) + ') 
				select date, observation from PivotTable where location = ''A' + cast(@counter as varchar) + ''''
	
	set @counter = @counter + 1
	
end

set @dynamicquery = @dynamicquery + ' select * from @finalTable'


exec sp_executesql @dynamicquery

Regards,
Yoga

Using the PIVOT command will help you solve your problem.
You can find Pivot syntax and pivot table example here.

Hi TGYOGA,

i used your code but it gives up a lot of null values.

What i want is on the same date i need all the related values.

But the date field also grows in the output along with the values!!!

 create table PivotTable  (Date varchar(10), Location varchar(2), observation varchar(20))  
   
 insert into PivotTable  
   
 select '25/6/2009', 'A1', 'HI'  
 union all select '25/6/2009', 'A2', 'Nil'  
 union all select '25/6/2009', 'A1', 'Xxx'  
 union all select '25/6/2009', 'A2', 'Yyy'  
 union all select '25/6/2009', 'A1', 'AAA'  
 union all select '25/6/2009', 'A3', '3AA'  
 union all select '25/6/2009', 'A3', '3VB'  
 union all select '22/6/2009', 'A4', '4AA'  
 union all select '22/6/2009', 'A4', '4VB'  
   
declare @Locations nvarchar(max)
declare @LocationValues nvarchar(max)
declare @sql nvarchar(max)
set @Locations = stuff((select distinct ',[' + Location +']' from PivotTable for xml path('')),1,1,'')
set @LocationValues = stuff((select distinct ',coalesce([' + Location +'],'''') as ' + Location from PivotTable for xml path('')),1,1,'')
set @sql = 'select Date,' + @LocationValues + ' from (select *,row_number() over (partition by Date,Location order by Date) as rn from Pivottable) p
pivot (max(observation) for Location in (' + @Locations + '))pvt'
exec(@sql)