求一存储过程，内容如下
表l_pxzx_kq
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[l_pxzx_kq]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[l_pxzx_kq]
GO
CREATE TABLE [dbo].[l_pxzx_kq] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[ssdm] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ssmc] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sfz] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ryxm] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[xmdm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[xmmc] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[s_kq] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[x_kq] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[scrq] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[l_pxzx_kq] ADD
CONSTRAINT [DF_l_pxzx_kq_ssdm] DEFAULT ('') FOR [ssdm],
CONSTRAINT [DF_l_pxzx_kq_sfz] DEFAULT ('') FOR [sfz],
CONSTRAINT [DF_l_pxzx_kq_xmdm] DEFAULT ('') FOR [xmdm],
CONSTRAINT [DF_l_pxzx_kq_scrq] DEFAULT (getdate()) FOR [scrq],
CONSTRAINT [PK_l_pxzx_kq] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
想实现列变行，就是把scrq里的值变成行，然后他的值是s_kq,x_kq,scrq的值是动态的
如下
原表
ssdm ssmc sfz ryxm xmdm xmmc s_kq x_kq scrq
02 A班 20209张三2013015电子小组 全勤全勤2013/1/11
02A班20209张三2013015电子小组 迟到旷课2013/1/12
02A班20209张三2013015电子小组 旷课迟到2013/1/13
02A班20209张三2013015电子小组 全勤全勤2013/1/14
02A班20209张三2013015电子小组 全勤全勤2013/1/1
根据给的参数xmdm，找出属于xmdm下的记录，实现结果如下
ssdm ssmc sfz ryxm 2013-1-1 2013-1-11 2013-1-12 2013-1-13 2013-1-14
02 A班 20209 张三 全勤 全勤 全勤 全勤 迟到 旷课 旷课 迟到 全勤 全勤
日期是动态的，不是固定的几天。
在线等
你自己关键字搜索看看吧
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-16 16:01:58
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[l_pxzx_kq]
if object_id('[l_pxzx_kq]') is not null drop table [l_pxzx_kq]
go
create table [l_pxzx_kq]([ssdm] varchar(2),[ssmc] varchar(3),[sfz] int,[ryxm] varchar(4),[xmdm] int,[xmmc] varchar(8),[s_kq] varchar(4),[x_kq] varchar(4),[scrq] datetime)
insert [l_pxzx_kq]
select '02','A班',20209,'张三',2013015,'电子小组','全勤','全勤','2013/1/11' union all
select '02','A班',20209,'张三',2013015,'电子小组','迟到','旷课','2013/1/12' union all
select '02','A班',20209,'张三',2013015,'电子小组','旷课','迟到','2013/1/13' union all
select '02','A班',20209,'张三',2013015,'电子小组','全勤','全勤','2013/1/14' union all
select '02','A班',20209,'张三',2013015,'电子小组','全勤','全勤','2013/1/1'
--------------开始查询--------------------------
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(scrq) + '=max(case when [scrq]='
+ QUOTENAME(scrq, '''') + ' then s_kq+'',''+x_kq else null end)'
FROM [l_pxzx_kq]
GROUP BY ssdm,ssmc,sfz,ryxm,xmdm,xmmc,scrq
EXEC('select ssdm,ssmc,sfz,ryxm,xmdm,xmmc'+@s+' from [l_pxzx_kq] group by ssdm,ssmc,sfz,ryxm,xmdm,xmmc')
--select * from [l_pxzx_kq]
----------------结果----------------------------
/*
ssdm ssmc sfz ryxm xmdm xmmc 01 1 2013 12:00AM 01 11 2013 12:00AM 01 12 2013 12:00AM 01 13 2013 12:00AM 01 14 2013 12:00AM
---- ---- ----------- ---- ----------- -------- ------------------ ------------------ ------------------ ------------------ ------------------
02 A班 20209 张三 2013015 电子小组 全勤,全勤 全勤,全勤 迟到,旷课 旷课,迟到 全勤,全勤
警告: 聚合或其他 SET 操作消除了 Null 值。
(1 行受影响)
*/
