Transposing?

[nipper1999@aol.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

[Simon Hayes]

Re: Transposing?


<nipper1999@aol .com> wrote in message
news:1106619215 .600714.81780@c 13g2000cwb.goog legroups.com...[color=blue]
>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
>[/color]

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