行转列时如何自动产生列名

[风一样的大叔]

不知道大家有没有研究过这个问题，如下：
table1
LocationCode CodeDetail
A02b 字段1
A02b 字段2
A02b 字段3
A02b 字段4
A02b 字段5

我在行转列的时候有如下语句：
pivot(max(Tallydata) for Tallyfield in (字段1,字段2,字段3,字段4,字段5)) as X

在存储过程或直接用sql语句是否可以依据table1里面的LocationCode字段的值直接自动写出
字段1,字段2,字段3,字段4,字段5 这样的格式

[發糞塗牆]

DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT  @s = @s + ',' + QUOTENAME(CodeDetail) + '=max(case when [CodeDetail]='
        + QUOTENAME(CodeDetail, '''') + ' then [CodeDetail] else ''0'' end)'
FROM    table1
GROUP BY LocationCode   
EXEC('select [LocationCode]'+@s+' from table1 group by [LocationCode]')

[风一样的大叔]

引用 1 楼 DBA_Huangzj 的回复:

SQL code?1234567DECLARE @s NVARCHAR(4000)SET @s = ''SELECT @s = @s + ',' + QUOTENAME(CodeDetail) + '=max(case when [CodeDetail]=' + QUOTENAME(CodeDetail, '''') + ' then [CodeDetai……

不是哦，我想生成一个字段1,字段2,字段3,字段4,字段5 这样的字符串，中间要有逗号隔开

[开启时代]

declare @F varchar(max),@str varchar(max) select @F=STUFF((select ','+quotename(codedetail) from #table3 for XML path('')),1,1,'') set @str='select * from TB pivot(max(Tallydata) for codedetail in ('+ @F +')) as X' exec(@str)

[發糞塗牆]

SELECT  a.LocationCode ,
        STUFF(( SELECT  ',' + CodeDetail
                FROM    table1 b
                WHERE   b.LocationCode = a.LocationCode
              FOR
                XML PATH('')
              ), 1, 1, '') 'CodeDetail'
FROM    table1 a
GROUP BY LocationCode

[风一样的大叔]

select top 1
STUFF(
(
SELECT (case CodeDetail when '' then '' else (CodeDetail + ',') end) FROM WLRS_Locational_Detail where locationcode=@locationcode
 FOR XML PATH('') 
),1,0,'') CodeDetail
from WLRS_Locational_Detail T

我自己写的，谢谢二位