j*****n
发帖数: 1781 | 1 Here is a question been asked...
先讨论一下第一题。我知道pivot的用法。但是这个考的好像和传统的不一样。我知道
的pivot一般都要和aggregate
function在一起用的。当然也可以不用aggregate function，但这样会有大量的null出
现。
我理解的题目意思是这样
1）把
colName1 colName2 colName3
s11 s12 s13
s21 s22 s23
s31 s32 s33
s41 s42 s43
变成
s11 s21 s31 s41
s12 s22 s32 s42
s13 s23 s33 s43
2） 然后用一个concatenate function 就整合成一个column了
第一步用一个procedure应该很好实现。但是用一句 SQL 做到实现整个table的转置，
我没找到答案。看你能不能提供点思路。
首先的一个困难，用case应该不行，应为根本没有unique之类的限制，case会把一列里
边等值的一起处理。 | j*****n
发帖数: 1781 | 2 I had a solution but sounds stupid, post here see if anyone has better idea.
with Data (c1, c2, c3)
AS (
SELECT 's11', 's12', 's13'
union all
select 's21', 's22', 's23'
union all
select 's31', 's32', 's33'
union all
select 's41', 's42', 's43'
)
SELECT [1] r1, [2] r2, [3] r3, [4] r4
FROM
(SELECT c1, row_number() over (order by c1) as RN
FROM data) AS SourceTable
PIVOT
(
min(c1)
FOR rn IN ([1], [2], [3], [4])
) AS PivotTable
union all
SELECT [1] r1, [2] r2, [3] r3, [4] r4
FROM
(SELECT c2, row_number() over (order by c2) as RN
FROM data) AS SourceTable
PIVOT
(
min(c2)
FOR rn IN ([1], [2], [3], [4])
) AS PivotTable
union all
SELECT [1] r1, [2] r2, [3] r3, [4] r4
FROM
(SELECT c3, row_number() over (order by c3) as RN
FROM data) AS SourceTable
PIVOT
(
min(c3)
FOR rn IN ([1], [2], [3], [4])
) AS PivotTable | B*****g
发帖数: 34098 | 3 re
idea.
【在 j*****n 的大作中提到】
: I had a solution but sounds stupid, post here see if anyone has better idea.
: with Data (c1, c2, c3)
: AS (
: SELECT 's11', 's12', 's13'
: union all
: select 's21', 's22', 's23'
: union all
: select 's31', 's32', 's33'
: union all
: select 's41', 's42', 's43'
|
|
