both examples look good to me, is there a way of making that dynamic? the amount of names are 10 in number at present and although that could change the...

that's great, thanks for your kind patience

Many thanks, I've tried your solutions and the first dynamic version from Cadavre works despite the fact syscolumns is in the query, when I substitute this with sys.columns it tells...

There is one more question regarding this, is there a way this can be achieved without specifying the column names (other than the date field) as I may need to...

thanks GilaMonster and Cadavre that's helped me a lot, I'm going to try and get my head round pivot and unpivot to see how this works, meanwhile you've both provided...

just in case it will help I've included a script to build a test table

USE test

GO

-- Creating Test Table

CREATE TABLE testtable(date datetime, jim VARCHAR(20), Dave VARCHAR(20),Tony VARCHAR(20),Mike...

Thank you so much for getting back to me so fast.I've googled your suggestion but can't see how that can help.

That is brilliant, thank you so much, it's exactly what I needed

Ok, as per Jeff's kind prompt I've created a script to create a test database to show what I'm after.

IF OBJECT_ID('mycalls','U') IS NOT NULL ...

My opologises, it seems I'd formatted the text wrong, the tables are made up like

table1

name as varchar(50)

[Number] as Varchar(20)

table2

[Number] as varchar(20)

[date of call] as datetime

In both tables the number field...

Thanks Lowell for your help on this, I've now got a couple of ideas to try

thanks for the quick reply, at the present I need this for SQL2000. The senario I want to use this in a webpage where you click on a letter, say...

Thanks for the reply Lars, not sure how to do either of those suggestions

That's great, works a treat,thanks Wayne and Jeff

Thats...