declare @sql varchar(1000)
declare @i int
set @sql =''
set @i = 0
select @i = @i+1,
@sql = @sql + ',{SERIAL_NUMBER_'+ltrim(@i)+'}'
from sys.tables
select stuff(@sql,1,1,'')
/*
{SERIAL_NUMBER_1},{SERIAL_NUMBER_2},{SERIAL_NUMBER_3},{SERIAL_NUMBER_4},{SERIAL_NUMBER_5},{SERIAL_NUMBER_6}
*/
select *,convert(nvarchar(50),null)'{SERIAL_NUMBER_5}' from tb
declare @max_rn int;
select @max_rn = (select count(*) from #t)
select @tsql='select [{PRODUCT_NAME}],[{LOT_NAME}],[{CARTON_SN}],[{CUSTOMER_CARTON_SN}],'+@tsql
+'[{SERIAL_NUMBER_'+ltrim(@max_rn+1)+']}'+....你要加几个就几个
+' from #t '
+' group by [{PRODUCT_NAME}],[{LOT_NAME}],[{CARTON_SN}],[{CUSTOMER_CARTON_SN}] '
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT '
SELECT @sql = @sql + 'MAX(CASE [XNAME] WHEN '''+[XNAME]+''' THEN '''' ELSE '''' END) AS '''+QUOTENAME('SERIAL_NUMBER_'+[XNAME])+''','
FROM (SELECT DISTINCT [XNAME] FROM [_TTT]) AS a
SELECT @sql = LEFT(@sql,LEN(@sql)-1) + ' FROM [_TTT] GROUP BY [XNAME]'
PRINT(@sql)
EXEC(@sql)
GO
