itemname checkvalue
左眼视力 1.0
右眼视力 1.5
左眼矫正视力 1.5
右眼矫正视力 1.5
结果(都是左眼/右眼打头,多个项目,不固定,需要动态)
视力 矫正视力
左眼 1.0 1.5
右眼 1.0 1.5
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-01 15:32:54
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([itemname] varchar(12),[checkvalue] numeric(2,1))
insert [huang]
select '左眼视力',1.0 union all
select '右眼视力',1.5 union all
select '左眼矫正视力',1.5 union all
select '右眼矫正视力',1.5
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(substring([itemname],3,LEN([itemname])))+'=max(case when substring([itemname],3,LEN([itemname]))='+quotename(substring([itemname],3,LEN([itemname])),'''')+' then [checkvalue] else 0 end)'
from [huang] group by substring([itemname],3,LEN([itemname]))
exec('select substring([itemname],1,2) as 视力'+@s+' from [huang] group by substring([itemname],1,2)')
----------------结果----------------------------
/*
视力 矫正视力 视力
---- --------------------------------------- ---------------------------------------
右眼 1.5 1.5
左眼 1.5 1.0
*/