Hi,

suppose the input is,

name1 id1 id2 id3 id4

name2 id1 id2 id3 id4

I want the output as in the database,

name1 id1

name1 id2

name1 id3

name1 id4

name2 id1

name2 id2

name2 id3

name2 id4

Please help me out on solving this in sql server 2000.

Please take the idea from one of my answer for "One row to Multiple Rows" in the same sql server thread.
The member name is "kkknnn" .

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

...

[last pivoted column] AS <column name>

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(

<aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;