MS SQL Server - "pivotting" a table...

Author

"pivotting" a table...

Dan Bass

2006-12-13, 5:18 am

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.

Uri Dimant

2006-12-13, 7:12 pm

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.
>

Dan Bass

2006-12-13, 7:12 pm

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...
>
>

Uri Dimant

2006-12-13, 7:12 pm

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...
>
>

Sponsored Links