怎样将SQL查出的字段分成两列啊
1201211
1201148
601
2000002
3000000
2000001
1231567
1200539
1231568
2000003
2000004
2000005
1231569
2000006
NULL
3000010
1231570
1231571
1231572
1231573
1231574
2000012
3000002
3000004
3000003
NULL
2000016
3000005
2000013
NULL
2000017
3000007
3000006
2000014
2000015
NULL
1231575
3000008
1231576
NULL
1231577
2000018
3000009
1231578
2000019
3000011
0
2000020
0
0
0
0
0
我要把以1开头和以2开头的数据查出来并分别形成一列 SELECT [ID]
,[APCode]
FROM [APProtal].[dbo].[RegisterInfo]
[解决办法]
----------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-12-28 10:56:31
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
--Apr 22 2011 11:57:00
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1201211 union all
select 1201148 union all
select 601 union all
select 2000002 union all
select 3000000 union all
select 2000001 union all
select 1231567 union all
select 1200539 union all
select 1231568 union all
select 2000003 union all
select 2000004 union all
select 2000005 union all
select 1231569 union all
select 2000006
--------------开始查询--------------------------
select
a.col,b.col
from
(
select id=row_number()over(order by getdate()),* from tb where left(col,1)=1
)a
left join
(
select id=row_number()over(order by getdate()),* from tb where left(col,1)=2
)b
on
a.id=b.id
----------------结果----------------------------
/* col col
----------- -----------
1201211 2000002
1201148 2000001
1231567 2000003
1200539 2000004
1231568 2000005
1231569 2000006
(6 行受影响)
*/
select case when colname like'1%' then colname end as 列1,
case when colname like'2%' then colname end as 列2
from table
