Reformat Data

I have a problem in that I need to reformat my data.

I have the data for part numbers to products from the selection.
e.g.

Part No Product Application
001 aaaa Standard
001 bbbb Option
002 aaaa Option
002 bbbb Standard
003 aaaa Option

and I wish reformat it into

Part No aaaa bbbb
001 Standard Option
002 Option Standard
003 Option Not App (Note missing from above selection)

There could be several products with various parts applicable / not applicableThis may be a little crude, and I'm sure that someone can point out better ways to do it. However it does work with the limited example that you describe.

The first part is just me building a temptable to select from

Hope it helps,

Brent

Create Table #TempParts (PartNo int, parttype varchar(5), description varchar(10))
insert #tempparts values(001, 'aaaa', 'standard')
insert #tempparts values(001, 'bbbb', 'option')
insert #tempparts values(002, 'bbbb', 'standard')
insert #tempparts values(002, 'aaaa', 'option')
insert #tempparts values(003, 'aaaa', 'option')

select PartNo,
Max(Case parttype when 'aaaa' then [description] end) as AAAA,
Max(Case parttype when 'bbbb' then [description] end) as BBBB
from #tempparts
group by partno|||Thanks for your reply but the problem is when I do the query on part no's I don't know at that time to what selection of Products they are applicable, and unfortunately there are hundreds of possible products.|||Do you have an equally large amount of Part No's as Products? What about Applications? Just trying to get an idea of the variables that need to be arranged in some sort of pivot table.

Reference another database without hardcoding the name of it..

Thanks Aaron
Sorry for not informing on the product, but i am working on 2000.
Some think like this could be nice:
declare @.database varchar(50)
set @.database = 'Database2'
select * from object_id(@.database).dbo.Test_Table
But your solution is like this:
declare @.database varchar(50)
declare @.sql varchar(1000)
set @.database = 'Database2'
set @.sql = 'select * from '+@.database+'.dbo.Test_Table'
executesql @.sql
Cheers
"Aaron Bertrand [SQL Server MVP]" wrote:

> In SQL Server 2005, you could create a synonym.
> In SQL Server 2000, the method I use is what you describe, get the name of
> the server and/or database and build a dynamic string. <yuck>
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
>
>But that's not valid T-SQL syntax.
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:B281CBFB-3B65-4A75-A728-DF6C151105A4@.microsoft.com...
> Thanks Aaron
> Sorry for not informing on the product, but i am working on 2000.
> Some think like this could be nice:
> declare @.database varchar(50)
> set @.database = 'Database2'
> select * from object_id(@.database).dbo.Test_Table
> But your solution is like this:
> declare @.database varchar(50)
> declare @.sql varchar(1000)
> set @.database = 'Database2'
> set @.sql = 'select * from '+@.database+'.dbo.Test_Table'
> executesql @.sql
> Cheers
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>