MSSql⾏列转换的Sql语法详解与实例
1.⾏列转换
原表（这⾥其实可以是个视图V_AnswerList）的数据结构，如下图所⽰：
编写存储过程：
CREATE PROCEDURE[dbo].[pro_Sum_1]
AS
BEGIN
DECLARE@sql varchar(8000)
SET@sql='select d_Name as 单位,s_Name as ⼯作⼈员'
SELECT@sql=@sql+', (case q_No when '''+ q_No +''' then a_Answer else '''+''+''' end) ['+ q_No +']'
FROM        (SELECT DISTINCT q_No
FROM          V_AnswerList) AS V_AnswerList
SELECT@sql=@sql+'from V_AnswerList'EXEC (@sql)
END
实现后的效果：
这⾥因为a_Answer为字符串类型，笔者不笑得使⽤那个聚合函数将其连接；如果a_Answer为int 型，在存储过程中你可以这样Sum（case q_No when ....）。
另外⼀个带参数的存储过程：
供参考：
CREATE PROCEDURE[dbo].[pro_Sum_2]
(
@s_No varchar(20)
)
AS
BEGIN
DECLARE@s_No_1varchar(20);
DECLARE@sql varchar(8000);
set
@s_No_1=@s_No;
SET
@sql='select d_Name as 单位,s_Name as ⼯作⼈员'
SELECT@sql=@sql+', (case q_No when '''+ q_No +''' then a_Answer else '''+''+''' end) ['+ q_No +']'
FROM        (SELECT DISTINCT q_No
FROM          V_AnswerList) AS V_AnswerList
SELECT@sql=@sql+'from V_AnswerList where s_No='+@s_No_1
EXEC (@sql)
sql存储过程实例END
1.--⾏列转换另例
原表:  姓名科⽬成绩
张三语⽂    80
张三数学    90
张三物理    85
李四语⽂    85
李四物理    82
李四英语    90
李四政治    70
王五英语    90
转换后的表：姓名数学物理英语语⽂政治
李四        0        82        90      85      70
王五        0          0        90      0        0
张三        90        85        0      80        0
实例:
create table cj  --创建表cj
(
ID      Int IDENTITY (1,1)    not null, --创建列ID，并且每次新增⼀条记录就会加1
Name    Varchar(50),
Subject  Varchar(50),
Result  Int,
primary key (ID)      --定义ID为表cj的主键
);
--Truncate table cj
--Select * from cj
Insert into cj
Select '张三','语⽂',80 union all
Select '张三','数学',90 union all
Select '张三','物理',85 union all
Select '李四','语⽂',85 union all
Select '李四','物理',82 union all
Select '李四','英语',90 union all
Select '李四','政治',70 union all
Select '王五','英语',90
--⾏列转换
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from cj) as cj  --把所有唯⼀的科⽬的名称都列举出来
Select @sql = @sql+' from cj group by name'
Exec (@sql)
2. ⾏列转换--合并
原表:  班级学号
1          1
1          2
1          3
2          1
2          2
3          1
转换后的表:  班级学号
1  1,2,3
2  1,2
3  1
实例:
Create table ClassNo  --创建表ClassNo
(
ID Int IDENTITY(1,1)  not null,  --创建列ID，并且每次新增⼀条记录就会加1
Class  Varchar(50),    --班级列
Number Varchar(50),    --学号列
Primary Key(ID)        --定义ID为表ClassNo的主键
)
;
--Truncate Table ClassNo
--Select * from ClassNo
Insert Into ClassNo
Select 1,1 Union all
Select 1,2 Union all
Select 1,3 Union all
Select 2,1 Union all
Select 2,2 Union all
Select 3,1
创建⼀个合并的函数
-
-Drop Function KFReturn
Create Function KFReturn(@Class Varchar(50))
Returns Varchar(8000)
as
Begin
Declare @str Varchar(8000)
Set @str = ''
Select @str = @str + cast(Number as Varchar(50))  + ',' from ClassNo Where Class = @Class Set @str = SubString(@str,1,len(@str)-1)
Return(@str)
End
--调⽤⾃定义函数得到结果
Select Distinct Class,dbo.KFReturn(Class) From ClassNo
3：列转⾏
--Drop Table ColumnToRow
Create table ColumnToRow
(
ID Int IDENTITY(1,1)  not null,  --创建列ID，并且每次新增⼀条记录就会加1
a  int,
b  int,
c  int,
d  int,
e  int,
f  int,
g  int,
h  int,
Primary Key(ID)        --定义ID为表ColumnToRow的主键
);
--Truncate Table ColumnToRow
--Select * from ColumnToRow
Insert Into ColumnToRow
Select 15,9,1,0,1,2,4,2 Union all
Select 22,34,44,5,6,7,8,7 Union all
Select 33,44,55,66,77,88,99,12
Declare @sql Varchar(8000)
Set @sql = ''
Select @sql = @sql + rtrim(name) + ' from ColumnToRow union all Select ' from SysColumns Where id = object_id('ColumnToRow') Set @sql = SubString(@sql,1,len(@sql)-70)
--70的长度就是这个字符串'from ColumnToRow union all Select ID from ColumnToRow union all Select '，因为它会把ID这⼀列的值也算进去，所以要把它截掉
Exec ('Select ' + @sql + ' from ColumnToRow')
4. 如何取得⼀个数据表的所有列名
⽅法如下：先从sysobjects系统表中取得数据表的systemid，然后再syscolumns表中取得该数据表的所有列名。
SQL语句如下：
Declare @objid int,@objname char(40)
set @objname = 'ColumnToRow'
--第1种⽅法
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
--或也可以写成
select name as 'Column_name' from syscolumns where id = @objid order by colid
--第2种⽅法:
Select name as 'Column_Name' from SysColumns where id = object_id(@objname) Order by colid
5. 通过SQL语句来更改⽤户的密码
修改别⼈的，需要sysadmin role
Exec Sp_password '原始密码','更改后密码','账号'
Exec sp_password null,ok,sa
6. 怎么判断出⼀个表的哪些字段不允许为空？
Declare @objname Varchar(50)
set @objname = 'ColumnToRow'
Select Column_Name from information_schema.Columns where is_nullable = 'No' and Table_Name = @objname
7. 如何在数据库⾥到含有相同字段的表？
a. 查已知列名的情况
Select a.name as Columnname,b.name as tablename from SysColumns a inner join sysobjects b on a.id = b.id
pe = 'U' and a.name = '您要查的字段名'
b. 未知列名查所有在不同表出现过的列名
Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s
Where s1.id = s.id and s.Type = 'U' and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)
8.查询第N⾏数据
假设id是主键：
select *
from (select top N * from 表) aa
where not exists(select 1 from (select top N-1 * from 表) bb where aa.id=bb.id)
9. SQL Server⽇期计算
a. ⼀个⽉的第⼀天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期⼀
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. ⼀年的第⼀天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第⼀天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个⽉的最后⼀天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后⼀天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本⽉的最后⼀天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本⽉的第⼀个星期⼀
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0) i. 本年的最后⼀天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。