sql convert vertical to horizontal

[azamsalam]

I am using SQL Server 2005. I have a data from SQL like this.

ID ID2 PartNo

75 23921 DENT
75 26145 PLGD
75 26145 PRRP
75 26145 SIST
75 57290 PRMD
75 57290 abc
75 57290 def
75 57290 ghi
75 57290 jkl

I need an output like this. (max Part4)

ID ID2 Part1 Part2 Part3 Part4

75 23921 DENT
75 26145 PLGD PRRP SIST
75 57290 PRMD abc def ghi

Thanks

[sknake]

This is commonly referred to as a pivot.

IF OBJECT_ID('tempdb..#Parts', 'U') IS NOT NULL DROP TABLE #Parts
Create Table #Parts
(
  ID int,
  ID2 int,
  PartNo varchar(10)
)
SET NOCOUNT ON
Insert Into #Parts (ID, ID2, PartNo) Values (75, 23921, 'DENT')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'PLGD')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'PRRP')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'SIST')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'PRMD')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'abc')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'def')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'ghi')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'jkl')
SET NOCOUNT OFF

Select ID, ID2,
Max((Case When RowNumber = 1 Then PartNo Else '' End)) As Part1,
Max((Case When RowNumber = 2 Then PartNo Else '' End)) As Part2,
Max((Case When RowNumber = 3 Then PartNo Else '' End)) As Part3,
Max((Case When RowNumber = 4 Then PartNo Else '' End)) As Part4
From
(
  Select ID, ID2, PartNo,
  ROW_NUMBER() OVER(PARTITION BY ID, ID2 ORDER BY PartNo ASC) AS RowNumber
  From #Parts
) As tbl
Group By ID, ID2

Results in:

ID          ID2         Part1      Part2      Part3      Part4
----------- ----------- ---------- ---------- ---------- ----------
75          23921       DENT                             
75          26145       PLGD       PRRP       SIST       
75          57290       abc        def        ghi        jkl

(3 row(s) affected)

[dhfg]

so what to do if you want an output like

ID          ID2         DENT       PLGD       PRRP       SIST
----------- ----------- ---------- ---------- ---------- ----------
75          23921       1          0          0          1
75          26145       1          0          1          0
75          57290       0          1          0          1

is OVER(Partition By) still applicable?