PIVOT Query

Hi,
I have a requirement for a query which returns as many columns as many
rows are present in the table.
Table looks like this
Parent_Assembly_id Child_Assembly_id
1 2
2 3
3 4
3 5
I need to transform the data to look like
Level1 Level2 Level3 Level4 .....
1 2 3 4
1 2 3 5
.Hi
I'd prefer to do such kind of things on the client side
CREATE TABLE #Test
(
Parent_Assembly_id INT NOT NULL,
Child_Assembly_id INT NOT NULL
)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (2,3)
INSERT INTO #Test VALUES (3,4)
INSERT INTO #Test VALUES (3,5)
SELECT T2.Parent_Assembly_id AS Level1,
T2.Child_Assembly_id AS Level2,
T.Parent_Assembly_id AS Level3,
T.Child_Assembly_id AS Level4
FROM #Test T JOIN #Test T1
ON T.Parent_Assembly_id=T1.Child_Assembly_id JOIN #Test T2 ON
T1.Parent_Assembly_id=T2.Child_Assembly_id
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:OANptPUUFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a requirement for a query which returns as many columns as many
> rows are present in the table.
> Table looks like this
> Parent_Assembly_id Child_Assembly_id
> 1 2
> 2 3
> 3 4
> 3 5
>
> I need to transform the data to look like
> Level1 Level2 Level3 Level4 .....
> 1 2 3 4
> 1 2 3 5
> .
>|||Hi,
Thanks for the quick reply.
The problem I am facing is that I would not know before hnd the number
of levels that are present in the database.
The number of levels can be N levels. Is there any way of achieving
this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtLIzWUUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'd prefer to do such kind of things on the client side
> CREATE TABLE #Test
> (
> Parent_Assembly_id INT NOT NULL,
> Child_Assembly_id INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (2,3)
> INSERT INTO #Test VALUES (3,4)
> INSERT INTO #Test VALUES (3,5)
> SELECT T2.Parent_Assembly_id AS Level1,
> T2.Child_Assembly_id AS Level2,
> T.Parent_Assembly_id AS Level3,
> T.Child_Assembly_id AS Level4
> FROM #Test T JOIN #Test T1
> ON T.Parent_Assembly_id=T1.Child_Assembly_id JOIN #Test T2 ON
> T1.Parent_Assembly_id=T2.Child_Assembly_id
>
>
> "Nishanth" <cvnishanth@.hotmail.com> wrote in message
> news:OANptPUUFHA.3152@.TK2MSFTNGP12.phx.gbl...
many
>|||Hi
Well , thus I said that it will done better on the client side
You will have to calculate how many leveles you have and according to the
number to build a query.
I'd suggest you to do some searching on internet for examples of Itzik
Ben-Gan to deal with hierarchy data
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:eGNFbEVUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Thanks for the quick reply.
> The problem I am facing is that I would not know before hnd the number
> of levels that are present in the database.
> The number of levels can be N levels. Is there any way of achieving
> this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtLIzWUUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> many
>