sql查询出的结果行列转化
查询如下：
SysID MainID Value
14788 276 511.0
14789 276 504.9
14790 276 516.4
14791 276 525.5
14792 276 516.2
14793 276 508.8
转化为：
MainID Value1 Value2 Value3
276 511.0 504.9 516.4
剩下的省略了
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-30 13:58:09
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([SysID] int,[MainID] int,[Value] numeric(4,1))
insert [huang]
select 14788,276,511.0 union all
select 14789,276,504.9 union all
select 14790,276,516.4 union all
select 14791,276,525.5 union all
select 14792,276,516.2 union all
select 14793,276,508.8
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('Value'+CAST(ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP) AS VARCHAR))+'=max(case when [MainID]='+quotename(MainID,'''')+' and SysID='+quotename(SysID,'''')+' then [Value] else null end)'
from [huang] group by MainID,SysID
exec('select [MainID]'+@s+' from [huang] group by [MainID]')
----------------结果----------------------------
/*
MainID Value1 Value2 Value3 Value4 Value5 Value6
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
276 511.0 504.9 516.4 525.5 516.2 508.8
*/
------解决方案--------------------
declare @tsql varchar(6000)
declare @total int
select @total= max(c) from (select count(1) c from [Table_5] group by [MainID])t
select @tsql=isnull(@tsql+',','')
+'max(case when rn='+rtrim(number)+' then Value else '''' end) ''Value'''
from master.dbo.spt_values