引导者本篇文章给大家谈谈sqlserver列转行，以及sqlserver行列转换多行多列对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。
本文目录一览：
SqlServer 列转行
sql server 请参阅下面亩纯的代码,列转行
sqlserver列转行方法分享
以下例子适用 sqlserver 2005版本及岩耐侍以上
create table A ( info1 varchar(30),
[2012] int,
[2008] int,
[2018] int,
粗吵[2013] int
)
go
insert into A values('A',8,null,null,20)
insert into A values('B',null,7,null,3)
insert into A values('C',12,4,null,null)
insert into A values('D',null,null,5,16)
go
---列行转换 适用于sql server 2005及以上版本
SELECT info1,nian,qty
from A
unpivot(qty for nian in([2012],[2008],[2018],[2013]))as test
GO
truncate table A
drop table A
[img]sqlserver列转行 查询
select * from A_14072201
ID USERID
46859 38130
46859 888
46811 38130
46811 37761
46470 37836
46470 37836
46430 37836
46430 37836
select ID,USERID from A_14072201
where ID in (select ID from (select ID,USERID from A_14072201 group by ID,USERID) as tableA group by ID having COUNT(*) = 2)
ID USERID
46859 38130
46859 888
46811 38130
46811 37761
select table1.ID,table1.USERID,table2.USERID
from (select ID,USERID from (select ID,USERID,ROW_NUMBER() over (order by ID) 昌察罩ROWNUMBER from A_14072201 where ID in (select ID from (select ID,USERID from A_14072201 group by ID,USERID) as tableA group by ID having COUNT(*) = 2)) as tableB where ROWNUMBER % 2 = 1) table1
inner join
(select ID,USERID from A_14072201 where ID in (select ID from (select ID,USERID from A_14072201 group by ID,USERID) as tableA group by ID having COUNT(*) 没春= 2)) table2
on table1.ID = table2.ID and table1.USERID table2.USERID
ID USERID USERID
46811 38130 37761
46859 38130 888
--结果是对耐闹的，写的有点乱，你捋一捋然后优化一下，优化不了就直接改成表值函数去处理，我工作去了，话说你这个问题是半夜2：39发的，要注意身体啊。
SQLSERVER 列转行
第一：你的时间维度表基本没有意义，微软SSIS中心认为时间维度至少猛销由日期构成主键。可以认为是最小基本业务颗粒。
来个Sample，更复杂的在我空间里。但是道理是一样的。都是借助动态SQL和一些函数。
----------------------------------------------------------------
/*
作者:Edwin
数据库:SQL SERVER 2005+
作用:指定时间区间的自然周有几天，如果垮年度，由外围验证
Version 1.0
Copyright (c) 2015, SQL SERVER 2008
*/
----------------------------------------------------------------
/*参数设定区域,参数为开始时间和结束时间*/
----------------------------------------------------------------
declare @FDate DateTime set @FDate = '2014-09-01 00:00:00.000'
declare @Edate DateTime set @Edate = '2015-01-01 00:00:00.000'
----------------------------------------------------------------
/*SQL主体*/
----------------------------------------------------------------
declare @WeekHeader nvarchar(max)
select @WeekHeader = coalesce(@WeekHeader+',['+cast(WeekOfYear as varchar)+']','['+cast(WeekOfYear as varchar)+ ']')
from
(
select WeekOfYear from Comn.Calendar where DatePerDay=@FDate and DatePerDay@Edate group by WeekOfYear
) M
declare @PivotSQL nvarchar(max) set @PivotSQL=N'
select
Year as 年份,'+@WeekHeader+'
from
(
select [Year],WeekOfYear,DatePerDay from [DT_WareHouse].[Comn].[Calendar] where DatePerDay=@FDate and DatePerDay@Edate
) M
pivot
(
count(DatePerDay) for [WeekOfYear] in('+@WeekHeader+')
) PVT'
exec sp_executesql @PivotSQL,N'@FDate datetime,@Edate datetime',@FDate,@Edate
执行结果：
在看一下时枝消游间维度表：
至于PVT标题别名问题，这个可以在时间维度表中创建字符串类型的第几周等样式的列来完成。
这种方式比较简单。
或是
----------------------------------------------------------------
/*
作者:Edwin
数据库:SQL SERVER 2005+
作用:指定时间区间的自然周销售,统计周期为某一桥吵年，如果垮年度，由外围验证
Version 1.0
Copyright (c) 2015, SQL SERVER 2008
*/
----------------------------------------------------------------
/*参数设定区域,参数为开始时间和结束时间*/
----------------------------------------------------------------
declare @FDate DateTime set @FDate = '2014-09-01 00:00:00.000'
declare @Edate DateTime set @Edate = '2015-01-01 00:00:00.000'
----------------------------------------------------------------
/*SQL主体*/
----------------------------------------------------------------
declare @WeekHeader nvarchar(max)
select @WeekHeader = coalesce(@WeekHeader+',['+cast(WeekOfYear as varchar)+']','['+cast(WeekOfYear as varchar)+ ']')
from
(
select ('第'+cast(WeekOfYear as varchar)+'周') as WeekOfYear from Comn.Calendar where DatePerDay=@FDate and DatePerDay@Edate group by WeekOfYear
) M
declare @PivotSQL nvarchar(max) set @PivotSQL=N'
select
Year as 年份,'+@WeekHeader+'
from
(
select [Year],(''第''+cast(WeekOfYear as varchar)+''周'') as WeekOfYear,DatePerDay from [DT_WareHouse].[Comn].[Calendar] where DatePerDay=@FDate and DatePerDay@Edate
) M
pivot
(
count(DatePerDay) for [WeekOfYear] in('+@WeekHeader+')
) PVT'
exec sp_executesql @PivotSQL,N'@FDate datetime,@Edate datetime',@FDate,@Edate
结果：
sql 列转行
CREATE TABLE T1(A NUMBER,
b NUMBER,
c NUMBER,
d NUMBER,
e NUMBER);
insert into t1 values(1,2,3,4,5);
select * from t1;
select decode(A,1,'A',null) KEY ,decode(A,1,1,null) VALUE
from t1
union all
select decode(B,2,'B',null),decode(B,2,2,null)
from t1
union all
select decode(C,3,'C',null),decode(C,3,3,null)
from t1
union all
select decode(D,4,'D',null),decode(D,4,4,null)
from t1
union all
select decode(E,5,'E',null),decode(E,5,5,null)
from t1;
关于sqlserver列转行和sqlserver行列转换多行多列的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。
