相同ID按条件连接成一条记录

Question

现有视图V:Aid     E     B
94D3    9     null
4FD5    7     1
4FD5    0     1
9E81    6     5
9E81    6     null
9E81    6     5
9E81    6     5
A701    1    10
A701    1    11
A701    1    12
A701    1    13
A701    2    20
A701    2    21
A701    2    22
A701    2    23
A701    3    30
A701    3    31
A701    3    32
A701    3    33
A701    4    40
A701    4    41
A701    4    42
A701    4    43
想要结果（需要写的SQL）Aid   E    E1    E2     E3   B    B1    B2    B3    B4    B5    B6    B7     B8   B9    B10   B11   B12   B13    B14  B15
94D3  9   null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null
4FD5  7    0    null  null   1    1     null  null  null  null  null  null  null  null  null  null  null  null  null  null
9E81  6    6    6      6     5    null   5     5    null  null  null  null  null  null  null  null  null  null  null  null
A701  1    2    3      4    10    11     12    13    20     21   22   23     30    31    32   33     40   41     42    43说明:
1.视图V中相同Aid的记录最多为16个(最少是1,即相同Aid的个数为1到16),是根据查询关联的表
数据动态变化的,最终要查询的结果为上面唯一Aid的数据集.2.其实返回结果集的列数只与相同Aid的最大个数有关(E字段请直接返回E-E3这4个字段，
B字段[B0-B15]如能动态根据相同Aid的最大个数来处理为好,实在不行,就请写死:
直接返回B-B15这16个字段)与字段E、B的值无关。3.实际中和E、B字段相同情况的至少各还有一个，这些字段须能按实现SQL中 E、B的
情况来处理.

Answer 1

--行列互转
--1、行换列
if object_id('Class') is not  null
    drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='    
   
    +quotename([Course],'''')+' then [Score] else 0 end)'
from 
    Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]
GO
--动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:
/*
Student 数学         物理         英语         语文
------- ----------- ----------- ----------- -----------
李四     77          85          65          65
张三     87          90          82          78(2 行受影响)
*/go
--加上总成绩(学科平均分)--2000方法:
--动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from 
    Class 
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select  @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] 
--isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学         物理         英语         语文         总成绩
------- ----------- ----------- ----------- ----------- -----------
李四     77          85          65          65          292
张三     87          90          82          78          337(2 行受影响)
*/go--2、列转行
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四     数学     77
李四     物理     85
李四     英语     65
李四     语文     65
张三     数学     87
张三     物理     90
张三     英语     82
张三     语文     78
*/

Answer 2

declare @T table (Aid varchar(4),E int,B int)
insert into @T
select '94D3',9,null union all
select '4FD5',7,1 union all
select '4FD5',0,1 union all
select '9E81',6,5 union all
select '9E81',6,null union all
select '9E81',6,5 union all
select '9E81',6,5 union all
select 'A701',1,10 union all
select 'A701',1,11 union all
select 'A701',1,12 union all
select 'A701',1,13 union all
select 'A701',2,20 union all
select 'A701',2,21 union all
select 'A701',2,22 union all
select 'A701',2,23 union all
select 'A701',3,30 union all
select 'A701',3,31 union all
select 'A701',3,32 union all
select 'A701',3,33 union all
select 'A701',4,40 union all
select 'A701',4,41 union all
select 'A701',4,42 union all
select 'A701',4,43;WITH maco AS 
(
select ROW_NUMBER() OVER(PARTITION BY Aid ORDER BY GETDATE()) AS rid,* from @T 
)
select Aid,
SUM(CASE rid WHEN 1 THEN E ELSE NULL END) AS E,
SUM(CASE rid WHEN 2 THEN E ELSE NULL END) AS E1,
SUM(CASE rid WHEN 3 THEN E ELSE NULL END) AS E2,
SUM(CASE rid WHEN 4 THEN E ELSE NULL END) AS E3,
SUM(CASE rid WHEN 1 THEN B ELSE NULL END) AS B,
SUM(CASE rid WHEN 2 THEN B ELSE NULL END) AS B1,
SUM(CASE rid WHEN 3 THEN B ELSE NULL END) AS B2,
SUM(CASE rid WHEN 4 THEN B ELSE NULL END) AS B3,
SUM(CASE rid WHEN 5 THEN B ELSE NULL END) AS B4,
SUM(CASE rid WHEN 6 THEN B ELSE NULL END) AS B5,
SUM(CASE rid WHEN 7 THEN B ELSE NULL END) AS B6,
SUM(CASE rid WHEN 8 THEN B ELSE NULL END) AS B7,
SUM(CASE rid WHEN 9 THEN B ELSE NULL END) AS B8,
SUM(CASE rid WHEN 10 THEN B ELSE NULL END) AS B9,
SUM(CASE rid WHEN 11 THEN B ELSE NULL END) AS B10,
SUM(CASE rid WHEN 12 THEN B ELSE NULL END) AS B11,
SUM(CASE rid WHEN 13 THEN B ELSE NULL END) AS B12,
SUM(CASE rid WHEN 14 THEN B ELSE NULL END) AS B13,
SUM(CASE rid WHEN 15 THEN B ELSE NULL END) AS B14,
SUM(CASE rid WHEN 16 THEN B ELSE NULL END) AS B15
FROM maco GROUP BY Aid
/*
Aid  E           E1          E2          E3          B           B1          B2          B3          B4          B5          B6          B7          B8          B9          B10         B11         B12         B13         B14         B15
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
4FD5 7           0           NULL        NULL        1           1           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
94D3 9           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
9E81 6           6           6           6           5           NULL        5           5           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
A701 1           1           1           1           10          11          12          13          20          21          22          23          30          31          32          33          40          41          42          43
*/

Answer 3

谢谢叶子MM, Be trying...

Answer 4

6楼太搞笑了,叶子同学在么?刚准备结贴,看到您写的SQL或许有些小瑕疵,截取最后
一行部分返回结果：
实际想要返回的：Aid   E    E1  E2  E3  
A701  1    2    3   4  您的SQL返回的是：Aid   E    E1  E2  E3  
A701  1    1    1   1  能劳驾优化一下么? 
提示：最后一行的实际情况：E与B的关系是一对四（即一个E的值对应4个B的值,与E,B各自的值具体是多少无关）,也就是说第一列A701到第四列A701必定相同(此例是四个1,可以看做一组)，[第二组]第5列到A701到到第八列A701必定相同（4个2）,第三四组同此.
实际只要按顺序[依次]返回每组的一个值就达到目的了.

Answer 5

maco_wang 叶子或者热心的高手们在么?嘿嘿...

Answer 6

谢谢9楼的TravyLee,貌似同姓哦,没准是叶子妹妹哦,偶很小的哦，哈哈...

Answer 7

go
if object_id('[TBL]') is not null 
drop table [TBL]
go
create table [TBL](
[Aid] varchar(4),
[E] int,
[B] int
)
go
insert [TBL]
select '94D3',9,null union all
select '4FD5',7,1 union all
select '4FD5',0,1 union all
select '9E81',6,5 union all
select '9E81',6,null union all
select '9E81',6,5 union all
select '9E81',6,5 union all
select 'A701',1,10 union all
select 'A701',1,11 union all
select 'A701',1,12 union all
select 'A701',1,13 union all
select 'A701',2,20 union all
select 'A701',2,21 union all
select 'A701',2,22 union all
select 'A701',2,23 union all
select 'A701',3,30 union all
select 'A701',3,31 union all
select 'A701',3,32 union all
select 'A701',3,33 union all
select 'A701',4,40 union all
select 'A701',4,41 union all
select 'A701',4,42 union all
select 'A701',4,43;with tracy
as(
select ROW_NUMBER()over(PARTITION BY Aid ORDER BY GETDATE()) as num,
* from [tbl]
), mcg
as(
select Aid,
    SUM(CASE num WHEN 1 THEN E ELSE NULL END) AS E,
    SUM(CASE num WHEN 2 THEN E ELSE NULL END) AS E1,
    SUM(CASE num WHEN 3 THEN E ELSE NULL END) AS E2,
    SUM(CASE num WHEN 4 THEN E ELSE NULL END) AS E3,
    SUM(CASE num WHEN 1 THEN B ELSE NULL END) AS B,
    SUM(CASE num WHEN 2 THEN B ELSE NULL END) AS B1,
    SUM(CASE num WHEN 3 THEN B ELSE NULL END) AS B2,
    SUM(CASE num WHEN 4 THEN B ELSE NULL END) AS B3,
    SUM(CASE num WHEN 5 THEN B ELSE NULL END) AS B4,
    SUM(CASE num WHEN 6 THEN B ELSE NULL END) AS B5,
    SUM(CASE num WHEN 7 THEN B ELSE NULL END) AS B6,
    SUM(CASE num WHEN 8 THEN B ELSE NULL END) AS B7,
    SUM(CASE num WHEN 9 THEN B ELSE NULL END) AS B8,
    SUM(CASE num WHEN 10 THEN B ELSE NULL END) AS B9,
    SUM(CASE num WHEN 11 THEN B ELSE NULL END) AS B10,
    SUM(CASE num WHEN 12 THEN B ELSE NULL END) AS B11,
    SUM(CASE num WHEN 13 THEN B ELSE NULL END) AS B12,
    SUM(CASE num WHEN 14 THEN B ELSE NULL END) AS B13,
    SUM(CASE num WHEN 15 THEN B ELSE NULL END) AS B14,
    SUM(CASE num WHEN 16 THEN B ELSE NULL END) AS B15
FROM tracy GROUP BY Aid)
SELECT *FROM mcg WHERE Aid<>'A701'
UNION ALL
select Aid,E,
case when E1=E then 2 else E1 end as E1,
case when E1=E then 3 else E2 end as E2,
case when E1=E then 4 else E3 end as E3,
B,B1,B2,B3,B4,B5,B6, B7,B8,B9,B10,B11,B12,B13,B14,B15 from mcg where Aid='A701'/*
Aid E E1 E2 E3 B B1 B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B12 B13 B14 B15
4FD5 7 0 NULL NULL 1 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
94D3 9 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
9E81 6 6 6 6 5 NULL 5 5 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
A701 1 2 3 4 10 11 12 13 20 21 22 23 30 31 32 33 40 41 42 43
*/简单的处理，貌似不怎么好用，不过还是贴出来给你看看

Answer 8

我给的那个Aid='A701'这个是可以通过子查询确定出来的

Answer 9

首先多谢您热心的帮助,因为过滤数据ID都是未知的(非对已有具体的某些数据)，如
出现SELECT *FROM mcg WHERE Aid<>'A701'等(具体的ID的话)或许就不能作为通用
(面向所有数据),但如果是特定的就这些个数，相信您的SQL也是能实现的. 

Answer 10

哦，刚看到您12,13楼的回复，关于12楼的疑问：1.啥都不说的了,这个是原数据表设计的严重缺陷(不知是3NF没没理解透还是...)！2. re:为什么四个1就要处理，四个六又不处理掉：
因为E最多有四个值,如果超出四个值，说明至少有2个是值是相同的(或者理解为同一组).