sql中常用动态的行转列
//取出源数据
select groupname,totalnum,inputdate,groupid into #temp from
(select count(*)as totalnum,p.groupid,g.groupname,convert(nvarchar(10),inputdate,120) as 'inputdate'
from person p left join admin_group g on p.groupid = g.groupid and deleteflag = '0'
where p.inactive='0'
group by p.groupid,g.groupname,convert(nvarchar(10),p.inputdate,120)) a
left join
(select a.groupid as areagroupid,b.groupname as area,b.areaid
from admin_group a inner join admin_group b on a.upperid = b.groupid) b on a.groupid=b.areagroupid
//第一种方式能够控制显示的数据的样式，速度比较快
declare @s nvarchar(max)
select @s = 'select groupname as 社区名,groupid'
select @s = @s + ',isnull(max(case when inputdate=''' + cast(inputdate as varchar(10)) + ''' then totalnum end),0) as ''' + cast(inputdate as varchar(10)) + '''' from #temp group by inputdate order by inputdate desc
select @s = @s + ' from #temp where groupid=71 group by groupname,groupid'
exec(@s)
//第二种使用pivot（sql2005以上版本中有）
declare @s nvarchar(max)
Select @s=isnull(@s+',','')+quotename(inputdate) from #temp group by inputdate order by inputdate desc
exec('select * from #temp pivot (max(totalnum) for inputdate in('+@s+'))b')
相关文档：
1. 存储过程(定义&编写)
l 创建存储过程
CREATE PROCEDURE storedproc1
AS
SELECT *
from tb_project
WHERE 预计工期<= 90
ORDER BY 预计工期 DESC
GO
exec storedproc1
GO
l 修改存储过程
ALTER PROCEDURE storedproc1
AS
SEL ......
--SQL高级程序设计：子查询
use AdventureWorks
GO
SELECT DISTINCT EmployeeID from HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL;
SELECT e.EmployeeID,FirstName,LastName
from HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID IN ......
在网上看到SQL字符串转16进制的语句，经过小小添加，现将SQL字符串与16进制互换的方法记录，以供以后查看。
--SQL char->HEX code
DECLARE @str VARCHAR(4000)
SET @str='SELECT * from dbo.TaskHistory' --Your sql char
DECLARE @i INT,@Asi INT,@ModS INT,@res VARCHAR(800),@Len INT,@Cres VARCHAR(4),@te ......
<%
Dim Fy_Url,Fy_a,Fy_x,Fy_Cs(),Fy_Cl,Fy_Ts,Fy_Zx
'---定义部份 头------
Fy_Cl = 1 '处理方式：1=提示信息,2=转向页面,3=先提示再转向
Fy_Zx = "Error.Asp" '出错时转向的页面
'---定义部份 尾------
On Error Resume Next
Fy_Url=Request.ServerVariables("QUER ......
