不知道大家有没有研究过这个问题,如下:
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 这样的格式
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 这样的格式
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]')
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
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我自己写的,谢谢二位