求一存储过程，内容如上

求一存储过程，内容如下
表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 张三全勤全勤全勤全勤迟到旷课旷课迟到全勤全勤
日期是动态的，不是固定的几天。
在线等
你自己关键字搜索看看吧
[解决办法]

引用:

看过了，但是一个日期下，怎么显示s_kq和x_kq

s_kq+x_kq 字符串连接都是字符型的吧？
[解决办法]

----------------------------
-- 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 &lt;X64&gt; (Build 7601: Service Pack 1, v.721)
--
----------------------------
--&gt; 测试数据：[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 行受影响)
*/

求一存储过程内容如上

热点推荐