有一人这样的表
A B C
123 125
45
145 41
147
654 75
要去掉每行的空列变成如下 A B C
145 123 125
147 41 45
654 75
要怎么写sql呢
A B C
123 125
45
145 41
147
654 75
要去掉每行的空列变成如下 A B C
145 123 125
147 41 45
654 75
要怎么写sql呢
select A,B,C from(
select row_number()over(order by getdate()) as id,A from tbl)a
full join(
select row_number()over(order by getdate()) as id,B from tbl)b
on a.id=b.id
full join(
select row_number()over(order by getdate()) as id,C from tbl)c
on a.id=c.id
<table>
<tr><td>A</td><td>B</td><td>C</td></tr>
<tr><td> </td><td>123</td><td>125</td></tr>
<tr><td> </td><td> </td><td>45</td></tr>
<tr><td>145</td><td>41</td><td> </td></tr>
<tr><td>147</td><td> </td><td> </td></tr>
<tr><td>654</td><td>75</td><td> </td></tr>
</table
变成
<table>
<tr><td>A</td><td>B</td><td>C</td></tr>
<tr><td>145</td><td>123</td><td>125</td></tr>
<tr><td>147</td><td>41</td><td>45</td></tr>
<tr><td>654</td><td>75</td><td> </td></tr>
</table
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL
)
GO
INSERT INTO [tabl] ([A],[B],[C]) VALUES (null ,123,125);
INSERT INTO [tabl] ([A],[B],[C]) VALUES (null ,null,45);
INSERT INTO [tabl] ([A],[B],[C]) VALUES (145 ,41,null);
INSERT INTO [tabl] ([A],[B],[C]) VALUES (147 ,null,null);
INSERT INTO [tabl] ([A],[B],[C]) VALUES (654 ,75,null);
GO
select * from [tabl]
GO
select A,B,C from(
select row_number()over(order by getdate()) as id,A from [tabl] where A is not null)a
full join(
select row_number()over(order by getdate()) as id,B from [tabl] where B is not null)b
on a.id=b.id
full join(
select row_number()over(order by getdate()) as id,C from [tabl] where C is not null)c
on a.id=c.id