----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-28 12:18:01
-- 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]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([userid] varchar(3),[sj] datetime,[state1] varchar(2))
insert [tb]
select '001','2014-10-22','好' union all
select '001','2014-10-23','差' union all
select '001','2014-10-24','好' union all
select '002','2014-10-22','好'
--------------开始查询--------------------------declare @sql varchar(500)
select @sql=isnull(@sql+',','')+QUOTENAME(CONVERT(varchar(15),sj,23)) from (select distinct sj from tb)A
set @sql='SELECT userid,'+@sql+' FROM TB pivot (max(state1) for sj IN ('+@sql+'))b'
EXEC ( @sql)
----------------结果----------------------------
/*
userid 2014-10-22 2014-10-23 2014-10-24
------ ---------- ---------- ----------
001 好 差 好
002 好 NULL NULL
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-28 12:18:01
-- 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]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([userid] varchar(3),[sj] datetime,[state1] varchar(2))
insert [tb]
select '001','2014-10-22','好' union all
select '001','2014-10-23','差' union all
select '001','2014-10-24','好' union all
select '002','2014-10-22','好'
--------------开始查询--------------------------declare @sql varchar(500)
select @sql=isnull(@sql+',','')+QUOTENAME(CONVERT(varchar(15),sj,23)) from (select distinct sj from tb)A
set @sql='SELECT userid,'+@sql+' FROM TB pivot (max(state1) for sj IN ('+@sql+'))b'
EXEC ( @sql)
----------------结果----------------------------
/*
userid 2014-10-22 2014-10-23 2014-10-24
------ ---------- ---------- ----------
001 好 差 好
002 好 NULL NULL
*/
我 打错了 我 运行 的时候 是 exec(@sql) 我看了下代码 和你的没有什么区别啊 为什么我的就 提示有错误呢
我把你的代码复制过去也不行。提示相同的错误 我的是sql 2008 应该支持 pivot啊
还有个问题请教 比如 希望显示是这样的只显示 日 例如 userid 22 23 24
001 好 差 好
002 好
我改了下代码
declare @sql varchar(500)
select @sql=isnull(@sql+',','')+sj from (select distinct cast(day(sj)as char(2))sj from tb)A
set @sql='SELECT userid,'+@sql+' FROM TB pivot (max(state1) for day(sj)sj IN ('+@sql+'))b'
exec(@sql)提示 仍然是 '(' 附近有语法错误。 麻烦再 帮 我 看下 非常 感谢
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([userid] varchar(3),[sj] datetime,[state1] varchar(2))
insert [tb]
select '001','2014-10-22','好' union all
select '001','2014-10-23','差' union all
select '001','2014-10-24','好' union all
select '002','2014-10-22','好'
--------------开始查询--------------------------declare @sql varchar(500)
select @sql=isnull(@sql+',','')+QUOTENAME((CAST(sj as varchar(2)))) from (select distinct DATEPART(DAY,sj)sj from tb)A
set @sql='SELECT userid,'+@sql+' FROM (select userid,DATEPART(DAY,sj)sj,[state1] from TB)TB pivot (max(state1) for sj IN ('+@sql+'))b'EXEC ( @sql)----------------结果----------------------------
/*
userid 22 23 24
------ ---- ---- ----
001 好 差 好
002 好 NULL NULL*/