[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

pivoting one column

NH

3/21/2007 4:25:00 PM

I am writing some dynamic sql and I need to 'pivot' a column of field names
from the syscolumns table;

I need to convert this:

name
FIELD1
FIELD2
FIELD3
FIELD4

To this:

'FIELD1,FIELD2,FIELD3,FIELD4'

I can do it fairly easily with VBS but before I do, is there a simple way of
doing it within SQL?

Thanks

NH
2 Answers

masri999

3/21/2007 6:08:00 PM

0

On Mar 21, 9:24 pm, NH <N...@discussions.microsoft.com> wrote:
> I am writing some dynamic sql and I need to 'pivot' a column of field names
> from the syscolumns table;
>
> I need to convert this:
>
> name
> FIELD1
> FIELD2
> FIELD3
> FIELD4
>
> To this:
>
> 'FIELD1,FIELD2,FIELD3,FIELD4'
>
> I can do it fairly easily with VBS but before I do, is there a simple way of
> doing it within SQL?
>
> Thanks
>
> NH

in SQL 2005 , if in SQL 200 use syscolumns table

select
max(case when column_id = 1 then name end) as field1,
max(case when column_id = 2 then name end) as field2,
max(case when column_id = 3 then name end) as field3,
max(case when column_id = 4 then name end) as field4
from sys.columns
where object_id = 4

bob

3/22/2007 12:08:00 PM

0

Or you can do it with a variable:

DECLARE @columns VARCHAR(100)

SELECT @columns = ISNULL( @columns + ', ', '' ) + name
FROM sys.columns
WHERE object_id = 4
ORDER BY column_id

SELECT @columns

wBob


"NH" wrote:

> I am writing some dynamic sql and I need to 'pivot' a column of field names
> from the syscolumns table;
>
> I need to convert this:
>
> name
> FIELD1
> FIELD2
> FIELD3
> FIELD4
>
> To this:
>
> 'FIELD1,FIELD2,FIELD3,FIELD4'
>
> I can do it fairly easily with VBS but before I do, is there a simple way of
> doing it within SQL?
>
> Thanks
>
> NH
>
>