真心请教一个SQL视图写法，使纵列变行列
答案:4 悬赏:30 手机版
解决时间 2021-11-13 01:02
- 提问者网友：溺爱和你
- 2021-11-12 15:26
真心请教一个SQL视图写法，使纵列变行列
最佳答案
- 五星知识达人网友：梦中风几里
- 2021-11-12 16:21
建个函数把
CREATE FUNCTION GetBStringFromAID(@AID nvarchar(100))
RETURNS nvarchar(1024)
AS
BEGIN
declare @Str varchar(1024)
set @str=''
select distinct @Str=@Str + B +' '
from 表
where A=@AID
return @Str
END
然后查询
select A,GetBStringFromAID(A)as B
from (select distinct A from 表)a
效果为
A B
a1 b1 b2
a2 b3 b4 b5
虽然只有两列，但显示的效果类似
CREATE FUNCTION GetBStringFromAID(@AID nvarchar(100))
RETURNS nvarchar(1024)
AS
BEGIN
declare @Str varchar(1024)
set @str=''
select distinct @Str=@Str + B +' '
from 表
where A=@AID
return @Str
END
然后查询
select A,GetBStringFromAID(A)as B
from (select distinct A from 表)a
效果为
A B
a1 b1 b2
a2 b3 b4 b5
虽然只有两列，但显示的效果类似
全部回答
- 1楼网友：鸽屿
- 2021-11-12 17:29
没看太明白，视图是两列吗？
- 2楼网友：煞尾
- 2021-11-12 17:07
明白楼主意思。但是貌似需要一个循环的逻辑才能实现啊。试图真不知道改如何实现。帮不上忙
- 3楼网友：患得患失的劫
- 2021-11-12 16:27
参考：怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
答案一、
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
答案一、
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯