34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_tbZJ(發糞塗牆)
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tb]
DROP TABLE TB
go
create table tb(
DataItemName nvarchar(20),
CheckValue char(10)
)
insert into tb(DataItemName,CheckValue)values('左眼OD裸眼视力','0.10')
insert into tb(DataItemName,CheckValue)values('右眼OD裸眼视力','0.10')
insert into tb(DataItemName,CheckValue)values('左眼OD球面镜','-4.50')
insert into tb(DataItemName,CheckValue)values('右眼OD球面镜','-5.50')
insert into tb(DataItemName,CheckValue)values('左眼OD柱镜','-1.50')
insert into tb(DataItemName,CheckValue)values('右眼OD柱镜','-1.50')
insert into tb(DataItemName,CheckValue)values('左眼OD轴位','5.00')
insert into tb(DataItemName,CheckValue)values('右眼OD轴位','180.00')
insert into tb(DataItemName,CheckValue)values('左眼OD最佳矫正视力','0.90')
insert into tb(DataItemName,CheckValue)values('右眼OD最佳矫正视力','0.90-1')
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(substring([DataItemName],3,LEN([DataItemName])))+'=max(case when substring([DataItemName],3,LEN([DataItemName]))='+quotename(substring([DataItemName],3,LEN([DataItemName])),'''')+' then [checkvalue] else null end)'
from [tb] group by substring([DataItemName],3,LEN([DataItemName]))
exec('select substring([DataItemName],1,2) as 视力'+@s+' from [tb] group by substring([DataItemName],1,2)')
----------------结果----------------------------
/*
视力 OD裸眼视力 OD球面镜 OD轴位 OD柱镜 OD最佳矫正视力
---- ---------- ---------- ---------- ---------- ----------
右眼 0.10 -5.50 180.00 -1.50 0.90-1
左眼 0.10 -4.50 5.00 -1.50 0.90
*/
----------------------------------------------------------------
-- 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
*/