Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Can PIVOT do this ?
Message
 
 
À
18/05/2010 09:53:04
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
01464982
Message ID:
01465019
Vues:
59
>Maybe it wasn't a good idea to start this thread usings ints in the example. AFAICS the real problem is that PIVOT needs an aggregating function and you can't do that with a uniqueidentifier.....

Here is dynamic Pivot using your example:
declare @Cols varchar(max), @SQL nvarchar(max), @ColsWithNames varchar(max)

select @Cols = coalesce(@Cols + ',','') + QUOTENAME(ColumnID), 
@ColsWithNames = coalesce(@ColsWithNames + ', ','') + QUOTENAME(ColumnID) + ' as Column' + CAST(Row as varchar(10)) 
 from (select CAST(ColumnID as varchar(36)) as ColumnID, 
ROW_NUMBER() over (order by  CAST(ColumnID as varchar(36))) as Row from test50 group by CAST(ColumnID as varchar(36))) X

--print @Cols

set @SQL = 'select RowID, ' + @ColsWithNames + 
' from (select cast(ID as varchar(36)) as ID, cast(ColumnID as varchar(36)) as ColumnID, RowID from test50) src 
PIVOT (min(ID) FOR ColumnID IN (' + @Cols + ')) pvt'

--print @SQL

execute (@SQL)
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform