+++++++++这个查询有点不同 ----------------

[u011150962]

 select  num from table  结果如下：
150
280
40
80
83
40
20
160
208
220
180

如果查询出下面的结果，也就是分四列， 想用末除的方法，不知道能不能实现，也不知道怎么写！
，，请大神帮忙
150     280    40     80
83      40     20     160
208     220    180

[reenjie]

你可以查看一下行專列

[reenjie]

引用 1 楼 reenjie 的回复:

你可以查看一下行專列

不好意思，是行轉列。

[u011150962]

还有人来吗？

[Tiger_Zhao]

IF OBJECT_ID('T1') IS NOT NULL
  DROP TABLE T1
GO

CREATE TABLE T1 (code varchar(1), num int)
GO
INSERT INTO T1
SELECT 'a',150 UNION ALL
SELECT 'b',280 UNION ALL
SELECT 'c',40 UNION ALL
SELECT 'd',80 UNION ALL
SELECT 'e',83 UNION ALL
SELECT 'f',40 UNION ALL
SELECT 'g',20 UNION ALL
SELECT 'h',160 UNION ALL
SELECT 'i',208 UNION ALL
SELECT 'j',220 UNION ALL
SELECT 'k',180 


SELECT r, [0], [1], [2], [3]
  FROM (
        SELECT num,
              (ROW_NUMBER() OVER(ORDER BY code)-1)/4 r,
              (ROW_NUMBER() OVER(ORDER BY code)-1)%4 c
          FROM T1
       ) P
 PIVOT ( AVG(num) FOR c IN ([0], [1], [2], [3])
       ) AS PVT

r                    0           1           2           3
-------------------- ----------- ----------- ----------- -----------
0                    150         280         40          80
1                    83          40          20          160
2                    208         220         180         NULL

[u011150962]

引用 4 楼 Tiger_Zhao 的回复:

IF OBJECT_ID('T1') IS NOT NULL
  DROP TABLE T1
GO

CREATE TABLE T1 (code varchar(1), num int)
GO
INSERT INTO T1
SELECT 'a',150 UNION ALL
SELECT 'b',280 UNION ALL
SELECT 'c',40 UNION ALL
SELECT 'd',80 UNION ALL
SELECT 'e',83 UNION ALL
SELECT 'f',40 UNION ALL
SELECT 'g',20 UNION ALL
SELECT 'h',160 UNION ALL
SELECT 'i',208 UNION ALL
SELECT 'j',220 UNION ALL
SELECT 'k',180 


SELECT r, [0], [1], [2], [3]
  FROM (
        SELECT num,
              (ROW_NUMBER() OVER(ORDER BY code)-1)/4 r,
              (ROW_NUMBER() OVER(ORDER BY code)-1)%4 c
          FROM T1
       ) P
 PIVOT ( AVG(num) FOR c IN ([0], [1], [2], [3])
       ) AS PVT

r                    0           1           2           3
-------------------- ----------- ----------- ----------- -----------
0                    150         280         40          80
1                    83          40          20          160
2                    208         220         180         NULL

多谢大神！！

[Tiger_Zhao]

问题解决就请结帖。