Transposing?

[nipper1999]

I have a set of data coming in from a text file that looks like:

Date ID Value
01/01/2005 1 Value1
01/01/2005 2 Value2
01/01/2005 3 Value3
01/01/2005 4 Value4
01/01/2005 5 Value5
01/01/2005 6 Value6
01/01/2005 7 Value7
01/02/2005 1 Value1
01/02/2005 2 Value2
01/02/2005 3 Value3
01/02/2005 4 Value4
01/02/2005 5 Value5
01/02/2005 6 Value6
01/02/2005 7 Value7

There are 450 "ID"s per day and there will be ~30 days per month that
repeat these 450 IDs and values. I could potentially reduce this to
between 80 and 100 IDs. I would like to create a table that looks
like:
Date ID1 ID2 ID3 ID4 ID5 etc...
01/01/2005 Value1 Value2 Value3 Value4 Value5
01/02/2005 Value1 Value2 Value3 Value4 Value5
01/03/2005 Value1 Value2 Value3 Value4 Value5

What is the best way to do this? Any built-in SQL commands to assist?
Any tricks with DTS?

Thanks!
Dave

[Simon Hayes]

<ni********@aol.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

I have a set of data coming in from a text file that looks like:

Date ID Value
01/01/2005 1 Value1
01/01/2005 2 Value2
01/01/2005 3 Value3
01/01/2005 4 Value4
01/01/2005 5 Value5
01/01/2005 6 Value6
01/01/2005 7 Value7
01/02/2005 1 Value1
01/02/2005 2 Value2
01/02/2005 3 Value3
01/02/2005 4 Value4
01/02/2005 5 Value5
01/02/2005 6 Value6
01/02/2005 7 Value7

There are 450 "ID"s per day and there will be ~30 days per month that
repeat these 450 IDs and values. I could potentially reduce this to
between 80 and 100 IDs. I would like to create a table that looks
like:
Date ID1 ID2 ID3 ID4 ID5 etc...
01/01/2005 Value1 Value2 Value3 Value4 Value5
01/02/2005 Value1 Value2 Value3 Value4 Value5
01/03/2005 Value1 Value2 Value3 Value4 Value5

What is the best way to do this? Any built-in SQL commands to assist?
Any tricks with DTS?

Thanks!
Dave

You're looking for a crosstab query:

http://www.aspfaq.com/show.asp?id=2462

If the number of IDs is fixed, then a TSQL solution is possible (see below),
although with 450 IDs you might want to write a loop to generate the SELECT
statement. If things become more complex, or if the number of IDs can vary,
then it would be worth looking at a front-end solution or reporting tool.

Simon

select
date,
max(case when ID = 1 then Value else null end) as 'Value1',
max(case when ID = 2 then Value else null end) as 'Value2',
max(case when ID = 3 then Value else null end) as 'Value3',
...
from
dbo.MyTable
group by
date
order by
date