关于坐标的数据的取得

Question

我有一个表,里面的数据有三个字段:RowPosition,ColPosition,Text.RowPosition|ColPosition|  Text
      1    |     1     |   R1C1
      2    |     2     |   R2C2
      3    |     3     |   R3C3
      4    |     4     |   R4C4
      1    |     2     |   R1C2
      2    |     1     |   R2C1我想把它取出来,重新生成一个表,结构如下:_ _ _ _ _ _ _ _ _ _ 
R1C1 R1C2 
R2C1 R2C2
          R3C3
               R4C4也就是说,RowPosition,ColPosition分别表示数据所在新table的行和列.
这个在SQL里能实现吗?

Answer 1

create table tb(RowPosition int,ColPosition int, [Text] varchar(10))
insert into tb values(1 , 1 , 'R1C1') 
insert into tb values(2 , 2 , 'R2C2') 
insert into tb values(3 , 3 , 'R3C3') 
insert into tb values(4 , 4 , 'R4C4') 
insert into tb values(1 , 2 , 'R1C2') 
insert into tb values(2 , 1 , 'R2C1')
goselect c1 , c2 from
(
select RowPosition,
  max(case ColPosition when 1 then [text] else '' end) c1,
  max(case ColPosition when 2 then [text] else '' end) c2
from tb where RowPosition in
(select RowPosition from tb group by RowPosition having count(*) > 1)
group by RowPosition
) t1
union all
select c1 = '' , c2 = [Text] from tb where RowPosition in
(select RowPosition from tb group by RowPosition having count(*) = 1)drop table tb /*
c1         c2         
---------- ---------- 
R1C1       R1C2
R2C1       R2C2
           R3C3
           R4C4（所影响的行数为 4 行）
*/

Answer 2

create table s(RowPosition int,ColPosition int,  T VARCHAR(20)) 
INSERT s SELECT       1    ,    1    ,  'R1C1' 
UNION ALL SELECT       2    ,    2    ,  'R2C2' 
UNION ALL SELECT       3    ,    3    ,  'R3C3' 
UNION ALL SELECT       4    ,    4    ,  'R4C4' 
UNION ALL SELECT       1    ,    2    ,  'R1C2' 
UNION ALL SELECT       2    ,    1    ,  'R2C1' DECLARE @sql VARCHAR(1000)SELECT @sql=ISNULL(@sql+',','')+' max(case when ColPosition='+LTRIM(ColPosition)+' then T else '''' end )['+LTRIM(ColPosition)+']' FROM (SELECT DISTINCT ColPosition FROM s)aa
exec('select RowPosition,'+@sql+' from s group by RowPosition')--result
/*RowPosition 1                    2                    3                    4                    
----------- -------------------- -------------------- -------------------- -------------------- 
1           R1C1                 R1C2                                      
2           R2C1                 R2C2                                      
3                                                     R3C3                 
4                                                                          R4C4*/

Answer 3

谢谢你了,但是你的SQL不具有通用性,数据稍微改一点就用不了了,结果也不是很满意.
还是要谢谢你.

Answer 4

你太强大了,这个结果是我想要的,但是有一点不满意,如果数据出现下面的情况:RowPosition|ColPosition|  Text
      1    |    1      |  R1C1
      2    |    2      |  R2C2
      3    |    3      |  R3C3
      4    |    4      |  R4C4
      1    |    2      |  R1C2
      2    |    1      |  R2C1 
      6    |    6      |  R6C6 即没有出现第5列的数据,筛选出的结果是
    列1  列2     列3       列4   列6
行1 R1C1 R1C2
行2 R2C1 R2C2
行3  R3C3
行4    R4C4
行6  R6C6我实际上想要:
    列1  列2     列3       列4   列5   列6
行1 R1C1 R1C2
行2 R2C1 R2C2
行3  R3C3
行4    R4C4
行5
行6        R6C6大侠再给看看

Answer 5

布局不是很好,修正:
    列1  |列2  |列3  |列4  |列6
行1 R1C1 |R1C2 |     |     |
行2 R2C1 |R2C2 |     |     |
行3      |     |R3C3 |     |
行4      |     |     |R4C4 |
行6      |     |     |     |R6C6我实际上想要:
    列1  |列2  |列3  |列4  |列5  |列6
行1 R1C1 |R1C2 |     |     |     |
行2 R2C1 |R2C2 |     |     |     |
行3      |     |R3C3 |     |     |
行4      |     |     |R4C4 |     |
行5      |     |     |     |     |
行6      |     |     |     |     |R6C6

Answer 6

create table s(RowPosition int,ColPosition int,  T VARCHAR(20)) 
INSERT s SELECT       1    ,    1    ,  'R1C1' 
UNION ALL SELECT       2    ,    2    ,  'R2C2' 
UNION ALL SELECT       3    ,    3    ,  'R3C3' 
UNION ALL SELECT       4    ,    4    ,  'R4C4' 
UNION ALL SELECT       1    ,    2    ,  'R1C2' 
UNION ALL SELECT       2    ,    1    ,  'R2C1' 
UNION ALL SELECT  6,6,'R6C6'
DECLARE @sql VARCHAR(1000)SELECT @sql=ISNULL(@sql+',','')+' max(case when ColPosition='+LTRIM(ColPosition)+' then T else '''' end )['+LTRIM(ColPosition)+']' FROM 
(SELECT DISTINCT langid ColPosition FROM s,MASTER.dbo.syslanguages s2 WHERE s2.langid>0 AND s2.langid<=colposition)aa
exec('select RowPosition,'+@sql+' from s group by RowPosition')
--result
/*RowPosition 1                    2                    3                    4                    5                    6                    
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
1           R1C1                 R1C2                                                                                
2           R2C1                 R2C2                                                                                
3                                                     R3C3                                                           
4                                                                          R4C4                                      
6                                                                                                                    R6C6*/

Answer 7

DROP TABLE s
create table s(RowPosition int,ColPosition int,  T VARCHAR(20)) 
INSERT s SELECT       1    ,    1    ,  'R1C1' 
UNION ALL SELECT       2    ,    2    ,  'R2C2' 
UNION ALL SELECT       3    ,    3    ,  'R3C3' 
UNION ALL SELECT       4    ,    4    ,  'R4C4' 
UNION ALL SELECT       1    ,    2    ,  'R1C2' 
UNION ALL SELECT       2    ,    1    ,  'R2C1' 
UNION ALL SELECT  6,6,'R6C6'
DECLARE @sql VARCHAR(1000)SELECT @sql=ISNULL(@sql+',','')+' max(case when ColPosition='+LTRIM(ColPosition)+' then T else '''' end )['+LTRIM(ColPosition)+']' FROM 
(SELECT DISTINCT langid ColPosition FROM s,MASTER.dbo.syslanguages s2 WHERE s2.langid>0 AND s2.langid<=colposition)aa
exec('select RowPosition,'+@sql+' from (select * from s union all select langid,null,null from  master.dbo.syslanguages where langid>0 and langid<(select max(RowPosition) from s))aa  group by RowPosition')
--result
/*RowPosition 1                    2                    3                    4                    5                    6                    
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
1           R1C1                 R1C2                                                                                
2           R2C1                 R2C2                                                                                
3                                                     R3C3                                                           
4                                                                          R4C4                                      
5                                                                                                                    
6                                                                                                                    R6C6
                                                                                                                  R6C6*/需要注意的是：我这里用的是syalanguages中的langid列，如果s表中的id最大值比langid最大值大，就有问题，为了保险起见，你还是根据需要作一个带自增列的临时表为好

Answer 8

declare @table table (rowposition int,colposition int,[text] nvarchar(10))
insert into @table select 1,1,'R1C1'
       union all   select 2,2,'R2C2'
       union all   select 3,3,'R3C3'
       union all   select 4,4,'R4C4'
       union all   select 1,2,'R1C2'
       union all   select 2,1,'R2C1'
select case when colposition=1 then [text] when colposition=2 then '    '+ [text] 
else '     '+[text] 
end from @table
order by right([text],2) R1C1
R2C1
    R1C2
    R2C2
     R3C3
     R4C4(6 行受影响)