请教一下各位SQL编程达人

hellograce

请教一下各位SQL编程达人，在SQL server2008中，如何把表A中的数据转换成表B?
Table A
ID        Suburb        Postcode
1        ABBOTSFORD,AIRPORT WEST,BRUNSWICK        3067,3042,3056
2        BOX HILL,MELBOURNE        3128,3000

Table B
ID        Suburb        Postcode
1        ABBOTSFORD        3067
1        AIRPORT WEST        3042
1        BRUNSWICK        3056
2        BOX HILL        3128
2        MELBOURNE        3000

（在表A中，suburb 和postcode的顺序是一一对应的）。
先谢谢各位不吝赐教了。分分感谢

gooderic1977

你需要一个function来split text by ","，然后把结果存到临时表里处理：
CREATE FUNCTION [dbo].[fn_SplitString] (
        @Text nText,
        @delimiter char(1)
)
RETURNS @split_string table
        (id int IDENTITY(1,1) NOT NULL, part_string varchar(8000) NULL)
AS
BEGIN
          DECLARE @n_of_delimiters AS int
          DECLARE @i AS int  
        DECLARE @pstr As Varchar(8000)
        DECLARE @StartCharacter As BigInt
        DECLARE @EndCharacter As BigInt
        DECLARE @NumberIterations As BigInt
        DECLARE @CurrentIteration As BigInt
        DECLARE @DataLength As Decimal(19,2)
        DECLARE @CharacterLength As BigInt

        SET @CharacterLength         = 3996
        SET @DataLength         = DATALENGTH (@Text) / 2                        -- Divide the length by 2 as UNICODE uses 2 bytes to store 1 character of data
        SET @NumberIterations         = CEILING(@DataLength / @CharacterLength)
        SET @CurrentIteration        = 1
        SET @StartCharacter         = 1
        SET @EndCharacter        = @CharacterLength

        WHILE @CurrentIteration <= @NumberIterations
        BEGIN
                SET @pstr = SUBSTRING(@Text,@StartCharacter,@EndCharacter)
                SET @pstr = SUBSTRING(@pstr,1,@CharacterLength)

                -- Remove the ended comma
                If Right(@pstr,1) = @delimiter Set @pstr = substring(@pstr,1,Len(@pstr)-1)
       
                SET @n_of_delimiters=DATALENGTH(REPLACE(@pstr,@delimiter,@delimiter+'_')) - DATALENGTH(@pstr)
                 SET @i = 0
                  WHILE @i < @n_of_delimiters
                  BEGIN
                            SET @i = @i + 1
       
                            INSERT INTO @split_string
                              SELECT LEFT(@pstr,CHARINDEX(@delimiter,@pstr)-1)
       
                            SET @pstr=SUBSTRING(@pstr,CHARINDEX(@delimiter,@pstr)+1,8000)
                  END
       
                  INSERT INTO @split_string VALUES(@pstr)
       
                SET @CurrentIteration         = @CurrentIteration + 1
                SET @StartCharacter         = (@CharacterLength * (@CurrentIteration-1)) + 1
                SET @EndCharacter        = @CharacterLength * (@CurrentIteration)

        END

        RETURN
END

--SELECT * FROM [dbo].[fn_SplitString]('a,b,c,d',',')

kawara

从没见过表A那种表

大飞熊

kawara 发表于 2013-6-12 15:03
从没见过表A那种表

有可能是Raw Data，导到数据库里面再转换。

yzh1999

gooderic1977 发表于 2013-6-12 15:01
你需要一个function来split text by ","，然后把结果存到临时表里处理：
CREATE FUNCTION [dbo].[fn_SplitS ...

顶一下2楼的正常做法。
还有一种2b做法：
把表a copy一份到表c，然后重复以下的事：
insert into b (
　　select id, suburb, left(4, postcode) from c where length(postcode) >= 4
　);

update c set postcode = (把postcode左4以及逗号去掉) where length(postcode) >= 4

delete from c where postcode = ''

stevenbian

小表直接导入ultredit, replace ',' by ctrl+enter
整理一下用列编辑直接写insert 就行啦 ：-）

鱼羊鲜

顶楼上

hellograce

大飞熊 发表于 2013-6-12 15:05
有可能是Raw Data，导到数据库里面再转换。

是的，表A是raw data, 我要把它转化成表B才方便处理

hellograce

stevenbian 发表于 2013-6-12 16:04
小表直接导入ultredit, replace ',' by ctrl+enter
整理一下用列编辑直接写insert 就行啦 ：-）
...

问题是其实表A不至两行，有几百行呢，所以要写程序来处理，不能全靠手工啊

stevenbian

hellograce 发表于 2013-6-14 12:59
问题是其实表A不至两行，有几百行呢，所以要写程序来处理，不能全靠手工啊 ...

呵呵，没用过超级编辑器吧。
几千行都可以，列模式和替代很给力的。
不用你一条一条去编辑。很适合你这种一次性的应用。

鱼羊鲜

hellograce 发表于 2013-6-14 12:59
问题是其实表A不至两行，有几百行呢，所以要写程序来处理，不能全靠手工啊 ...

几百行，我还以为几百万行呢

hellograce

stevenbian 发表于 2013-6-14 13:31
呵呵，没用过超级编辑器吧。
几千行都可以，列模式和替代很给力的。
不用你一条一条去编辑。很适合你这种 ...

没用过ultreedit. Google了一下，好像下载不免费啊？

hellograce

鱼羊鲜 发表于 2013-6-14 14:50
几百行，我还以为几百万行呢

呵呵，我这种菜鸟，先拿几百行练练手吧

stevenbian

hellograce 发表于 2013-6-14 15:33
没用过ultreedit. Google了一下，好像下载不免费啊？

你先试用1个月呗。
好的话让老板买或者自己偷偷找注册码。

stevenbian

hellograce 发表于 2013-6-14 15:34
呵呵，我这种菜鸟，先拿几百行练练手吧

恩，用惯了就会觉得很方便。
根据你的例子
1.开一个UE窗口
2. select id||','||replace(suburb, ',', '''～'||id||',''') from a
3. 把结果复制进UE
4. 用ctrl+enter替代～
5.用列编辑前面加insert into b （id,suburb,postcode) values (
6.另开一个UE窗口
7. select postcode from b;
8. 把结果复制进UE
9. 用ctrl+enter替代，
10.用列编辑前面加个，‘后面加’）；
11.用列复制到前面那个文本。
12.保存脚本去运行吧。

预计5分钟吧，比写个帖子都快
适合菜鸟，不用写程序 ：-）

北风

stevenbian 发表于 2013-6-12 16:04
小表直接导入ultredit, replace ',' by ctrl+enter
整理一下用列编辑直接写insert 就行啦 ：-）
...

好方法

haidai