"pivotting" a table...

Greetings!

I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.

Basically something like this:

ID  Description
----------------
1    Bob
2    Franks
3    Harry

And I'd like this:

1            2            3
-----------------------------
Bob        Franks    Harry


I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?

Thanks.
Daniel.

Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))

GO

INSERT INTO #tmp VALUES (1,'Bob')

INSERT INTO #tmp VALUES(2,'Franks')

INSERT INTO #tmp VALUES(3,'Harry')



SELECT *

FROM #tmp

PIVOT

(

MAX(Description)

FOR ID IN([1],[2],[3])

) AS PVT

"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@TK2MSFTNGP03.phx.gbl...
> Greetings!
>
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
>
> Basically something like this:
>
>       ID  Description
>      ----------------
>        1    Bob
>        2    Franks
>        3    Harry
>
> And I'd like this:
>
>    1            2            3
> -----------------------------
>    Bob        Franks    Harry
>
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
>
> Thanks.
> Daniel.
>

Thanks Uri!


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
>
> GO
>
> INSERT INTO #tmp VALUES (1,'Bob')
>
> INSERT INTO #tmp VALUES(2,'Franks')
>
> INSERT INTO #tmp VALUES(3,'Harry')
>
>
>
> SELECT *
>
> FROM #tmp
>
> PIVOT
>
> (
>
> MAX(Description)
>
> FOR ID IN([1],[2],[3])
>
> ) AS PVT
>
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@TK2MSFTNGP03.phx.gbl... 
>
>

Dan
More dynamic
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

DECLARE

@cols AS NVARCHAR(MAX),

@y AS INT,

@sql AS NVARCHAR(MAX);



SET @cols = STUFF(

(SELECT N',' + QUOTENAME(y) AS [text()]

FROM (SELECT id AS y FROM #tmp) AS Y

ORDER BY y

FOR XML PATH('')),

1, 1, N'');



SET @sql = N'SELECT *

FROM (SELECT *

FROM #tmp) AS D

 PIVOT(MAX(Descriptio
n) FOR id IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;









"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@TK2MSFTNGP02.phx.gbl... 
>
>