SQL合并多行（N)数据成（1-N）行
本帖最后由 talhanwenhui 于 2012-11-08 15:33:35 编辑 各位技术达人，大家好。
我遇到以下问题：
环境： SQL Server 2005
请教一个问题，我使用pivot方法，进行行转列，得到以下结果
现在想通过一种方法，尽量使行数减少，同时查看也更方便，如下图，请大家帮帮忙，怎么通过SQL合并好？谢谢
[最优解释]
select ISNULL(a.编号,isnull(b.编号,isnull(c.编号,ISNULL(d.编号,e.编号)))),各个时间点补全...
from TB1 as a full join TB2 as b on a.编号=b.编号 and a.rowid=b.rowid
full join TB3 as c on on a.编号=c.编号 and a.rowid=c.rowid
full join TB3 as d on on a.编号=d.编号 and a.rowid=d.rowid
full join TB3 as e on on a.编号=e.编号 and a.rowid=e.rowid
[其他解释]
select 编号 ,max(字段)
from tb
group by 编号
select 编号,[14:00],ROW_NUMBER() over(partition by 编号 order by [14:00]) as rowid
from TB
where not [14:00] is null
group by 编号,[14:00]
1 null c
1 d null
按照你说的方法，最后合并结果为可能为:
编号 14:00 15:00
1 d c
而我要的效果是
编号 14:00 15:00
1 a b
1 d c
[其他解释]
教师 ,
F2
) A
FULL JOIN ( SELECT 编号 ,
教师 ,
F3 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F3 ) AS rowid
FROM tbl
WHERE NOT F3 IS NULL
GROUP BY 编号 ,
教师 ,
F3
) B ON A.编号 = B.编号
AND A.教师 = B.教师
AND A.rowid = B.rowid
FULL JOIN ( SELECT 编号 ,
教师 ,
F4 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F4 ) AS rowid
FROM tbl
WHERE NOT F4 IS NULL
GROUP BY 编号 ,
教师 ,
F4
) C ON A.编号 = C.编号
AND A.教师 = C.教师
AND A.rowid = C.rowid
FULL JOIN ( SELECT 编号 ,
教师 ,
F5 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F5 ) AS rowid
FROM tbl
WHERE NOT F5 IS NULL
GROUP BY 编号 ,
教师 ,
F5
) D ON A.编号 = D.编号
AND A.教师 = D.教师
AND A.rowid = D.rowid
FULL JOIN ( SELECT 编号 ,
教师 ,
F6 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F6 ) AS rowid
FROM tbl
WHERE NOT F6 IS NULL
GROUP BY 编号 ,
教师 ,
F6
) E ON A.编号 = E.编号
AND A.教师 = E.教师
AND A.rowid = E.rowid
) AS F
WHERE 编号 IS NOT NULL
AND ( f2 IS NOT NULL
AND f3 IS NOT NULL
AND f4 IS NOT NULL
AND f5 IS NOT NULL
AND f6 IS NOT NULL
)
ORDER BY F.编号 DESC ,
教师
[其他解释]
F5 ,
F6 这里编号和教师 一定要取全 ，怎么可能有空编号和空教师呢 ，对不对 ？呵呵
[其他解释]
教师 ORDER BY F3 ) AS rowid
FROM tbl
WHERE NOT F3 IS NULL
GROUP BY 编号 ,
教师 ,
F3
) B ON A.编号 = B.编号
AND A.教师 = B.教师
AND A.rowid = B.rowid
FULL outer JOIN ( SELECT 编号 ,
教师 ,
F4 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F4 ) AS rowid
FROM tbl
WHERE NOT F4 IS NULL
GROUP BY 编号 ,
教师 ,
F4
) C ON A.编号 = C.编号
AND A.教师 = C.教师
AND A.rowid = C.rowid
FULL outer JOIN ( SELECT 编号 ,
教师 ,
F5 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F5 ) AS rowid
FROM tbl
WHERE NOT F5 IS NULL
GROUP BY 编号 ,
教师 ,
F5
) D ON A.编号 = D.编号
AND A.教师 = D.教师
AND A.rowid = D.rowid
FULL OUTER JOIN ( SELECT 编号 ,
教师 ,
F6 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F6 ) AS rowid
FROM tbl
WHERE NOT F6 IS NULL
GROUP BY 编号 ,
教师 ,
F6
) E ON A.编号 = E.编号
AND A.教师 = E.教师
AND A.rowid = E.rowid
) AS F
ORDER BY F.编号 DESC ,
教师
返回值为
然后对以上SQL语句，再添加一个查询条件,返回的结果还是这样
现在是想要把F2-F6 为 NULL 的都排除掉。
[其他解释]
F3 ,
F4 ,
F5 ,
F6
FROM ( SELECT 编号 ,
教师 ,
F2 ,
ROW_NUMBER() OVER ( PARTITION BY 编号, 教师 ORDER BY F2 ) AS rowid
FROM tbl
WHERE NOT F2 IS NULL
GROUP BY 编号 ,
教师 ,
F2
) A
FULL outer JOIN ( SELECT 编号 ,
教师 ,
F3 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F3 ) AS rowid
FROM tbl
WHERE NOT F3 IS NULL
GROUP BY 编号 ,
教师 ,
F3
) B ON A.编号 = B.编号
AND A.教师 = B.教师
AND A.rowid = B.rowid
FULL outer JOIN ( SELECT 编号 ,
教师 ,
F4 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F4 ) AS rowid
FROM tbl
WHERE NOT F4 IS NULL
GROUP BY 编号 ,
教师 ,
F4
) C ON A.编号 = C.编号
AND A.教师 = C.教师
AND A.rowid = C.rowid
FULL outer JOIN ( SELECT 编号 ,
教师 ,
F5 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F5 ) AS rowid
FROM tbl
WHERE NOT F5 IS NULL
GROUP BY 编号 ,
教师 ,
F5
) D ON A.编号 = D.编号
AND A.教师 = D.教师
AND A.rowid = D.rowid
FULL OUTER JOIN ( SELECT 编号 ,
教师 ,
F6 ,
ROW_NUMBER() OVER ( PARTITION BY 编号,
教师 ORDER BY F6 ) AS rowid
FROM tbl
WHERE NOT F6 IS NULL
GROUP BY 编号 ,
教师 ,
F6
) E ON A.编号 = E.编号
AND A.教师 = E.教师
AND A.rowid = E.rowid
) AS F
WHERE NOT F2 IS NULL OR NOT F3 IS NULL OR NOT F4 IS NULL OR NOT F5 IS NOT OR NOT F6 IS NULL
ORDER BY F.编号 DESC ,
教师
--即使把查询条件改成
WHERE NOT F2 IS NULL AND NOT F3 IS NULL AND NOT F4 IS NULL AND NOT F5 IS NOT AND NOT F6 IS NULL
还是出现F2-F6 同时为 NULL 的行
[其他解释]
