表结构如下:
GUID(varchar(36)) itemA(varchar(36)) itemB(varchar(36)) time(DateTime)
1001a a ai 2014/03/25
1001b b bi 2014/03/25
要求显示的结果如下:
time a b===>表头
2014/03/25 ai bi===>列值
求达人，求解决方案
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-25 14:31:22
-- 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]([GUID] nvarchar(10),[itemA] nvarchar(2),[itemB] nvarchar(4),[time] datetime)
insert [huang]
select '1001a','a','ai','2014/03/25' union all
select '1001b','b','bi','2014/03/25'
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([itemA])+'=max(case when [itemA]='+quotename([itemA],'''')+' then [itemB] else null end)'
from [huang] group by [itemA]--[time]
exec('select [time]'+@s+' from [huang] group by [time]')
----------------结果----------------------------
/*
time a b
----------------------- ---- ----
2014-03-25 00:00:00.000 ai bi
*/
------解决方案--------------------
SELECT
[time],
MAX(CASE WHEN [itemA]='a' THEN [itemB] ELSE NULL END) AS a,
MAX(CASE WHEN [itemA]='b' THEN [itemB] ELSE NULL END) AS b
FROM
tb
GROUP BY
[time]