SQL 问题如何分列

jeff_hua 2009-12-09 02:29:06

一个数据字段描述表
tb1
SEQ Meno
NO1 ITEM CT数据采集:WO;Item;ProcessID;Second
NO2 Machine Code数据采集: LineCode;
NO3 文具领用数据采集:ItemBarCode;EmpNo;

tb2

datetime AuditDetails
2009-12-9 8:01 ITEM CT数据采集:10422;042032;p12203-12;42;
2009-12-10 8:01 ITEM CT数据采集:10428;040233;p14024-14;22;
2009-12-11 8:02 ITEM CT数据采集:15022;051220;p15022-03;91;
2009-12-12 8:02 文具领用数据采集:104222233;10220;
2009-12-13 8:02 文具领用数据采集:104222233;10221;
2009-12-14 8:02 文具领用数据采集:104222233;10222;
2009-12-15 8:02 Machine Code 数据采集:LN0021292;
2009-12-16 8:02 Machine Code 数据采集:LN0021293;
2009-12-17 8:02 Machine Code 数据采集:LN0021294;
2009-12-18 8:02 Machine Code 数据采集:LN0021295;
2009-12-19 8:02 Machine Code 数据采集:LN0021296;
2009-12-20 8:02 Machine Code 数据采集:LN0021297;
2009-12-21 8:02 Machine Code 数据采集:LN0021298;
2009-12-22 8:02 Machine Code 数据采集:LN0021299;
2009-12-23 8:02 Machine Code 数据采集:LN0021300;
2009-12-24 8:02 Machine Code 数据采集:LN0021301;
2009-12-25 8:02 ITEM CT数据采集:10422;042032;p12203-15;30;
2009-12-26 8:02 ITEM CT数据采集:10428;040233;p14024-19;82;
2009-12-27 8:02 ITEM CT数据采集:15022;051220;p15022-09;22;

希望根据数据描述表的列得到
WO Item ProcessID Second
14022 042032 p12203-12 42
14028 040233 p14024-14 22
15022 051220 p15022-03 91
14022 042032 p12203-15 30
14028 040233 p14024-19 82
15022 051220 p15022-09 22

...全文

109 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

jeff_hua 2009-12-09

打赏
举报

只是列名还是要手动去改

jeff_hua 2009-12-09

打赏
举报

这次看懂了。， SPLIt再搞一下COL1就是了

jeff_hua 2009-12-09

打赏
举报

你的意思吧 ":" 改成 ";" 然后再去截。是个办法。

--小F-- 2009-12-09

打赏
举报

你自己把别名改下
把COL1截取下

--小F-- 2009-12-09

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-12-09 14:40:56

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb1]

if object_id('[tb1]') is not null drop table [tb1]

go 

create table [tb1]([SEQ] varchar(3),[Meno] varchar(39))

insert [tb1]

select 'NO1','ITEMCT数据采集:WO;Item;ProcessID;Second' union all

select 'NO2','MachineCode数据采集:LineCode' union all

select 'NO3','文具领用数据采集:ItemBarCode;EmpNo'

--> 测试数据：[tb2]

if object_id('[tb2]') is not null drop table [tb2]

go 

create table [tb2]([datetime] datetime,[AuditDetails] varchar(40))

insert [tb2]

select '2009-12-9 8:01','ITEMCT数据采集:10422;042032;p12203-12;42' union all

select '2009-12-10 8:01','ITEMCT数据采集:10428;040233;p14024-14;22' union all

select '2009-12-11 8:02','ITEMCT数据采集:15022;051220;p15022-03;91' union all

select '2009-12-12 8:02','文具领用数据采集:104222233;10220' union all

select '2009-12-13 8:02','文具领用数据采集:104222233;10221' union all

select '2009-12-14 8:02','文具领用数据采集:104222233;10222' union all

select '2009-12-15 8:02','MachineCode数据采集:LN0021292' union all

select '2009-12-16 8:02','MachineCode数据采集:LN0021293' union all

select '2009-12-17 8:02','MachineCode数据采集:LN0021294' union all

select '2009-12-18 8:02','MachineCode数据采集:LN0021295' union all

select '2009-12-19 8:02','MachineCode数据采集:LN0021296' union all

select '2009-12-20 8:02','MachineCode数据采集:LN0021297' union all

select '2009-12-21 8:02','MachineCode数据采集:LN0021298' union all

select '2009-12-22 8:02','MachineCode数据采集:LN0021299' union all

select '2009-12-23 8:02','MachineCode数据采集:LN0021300' union all

select '2009-12-24 8:02','MachineCode数据采集:LN0021301' union all

select '2009-12-25 8:02','ITEMCT数据采集:10422;042032;p12203-15;30' union all

select '2009-12-26 8:02','ITEMCT数据采集:10428;040233;p14024-19;82' union all

select '2009-12-27 8:02','ITEMCT数据采集:15022;051220;p15022-09;22'

--------------开始查询--------------------------

SELECT 

       COL1 = PARSENAME(REPLACE(AuditDetails,';','.'),4),   ----替换一下 '.' 因为 parsename 只认 '.' 

       COL2 = PARSENAME(REPLACE(AuditDetails,';','.'),3),

       COL3 = PARSENAME(REPLACE(AuditDetails,';','.'),2),

       COL4 = PARSENAME(REPLACE(AuditDetails,';','.'),1)

FROM

       tb1 a,tb2 b

where

       substring(a.Meno,1,charindex(':',a.Meno)-1)=substring(b.AuditDetails,1,charindex(':',b.AuditDetails)-1)

and

       substring(a.Meno,1,charindex(':',a.Meno)-1)='ITEMCT数据采集'

----------------结果----------------------------

/* COL1                                                                                                                             COL2                                                                                                                             COL3                                                                                                                             COL4

-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

ITEMCT数据采集:10422                                                                                                                 042032                                                                                                                           p12203-12                                                                                                                        42

ITEMCT数据采集:10428                                                                                                                 040233                                                                                                                           p14024-14                                                                                                                        22

ITEMCT数据采集:15022                                                                                                                 051220                                                                                                                           p15022-03                                                                                                                        91

ITEMCT数据采集:10422                                                                                                                 042032                                                                                                                           p12203-15                                                                                                                        30

ITEMCT数据采集:10428                                                                                                                 040233                                                                                                                           p14024-19                                                                                                                        82

ITEMCT数据采集:15022                                                                                                                 051220                                                                                                                           p15022-09                                                                                                                        22



(6 行受影响)



*/

fwacky 2009-12-09

打赏
举报

帮顶！

jeff_hua 2009-12-09

打赏
举报

我有一个 SPLIt函数在这里是可以用

CREATE FUNCTION [dbo].[split]
(@str nvarchar(4000),@code varchar(10),@no int )
RETURNS varchar(200)
AS
BEGIN

declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen=len(@code)
set @count=0
set @indexb=1

if @no=0
if charindex(@code,@str,@indexb)<>0
return left(@str,charindex(@code,@str,@indexb)-1)
else
return @str

while charindex(@code,@str,@indexb)<>0
begin
set @count=@count+1
if @count=@no
break
set @indexb=@intLen+charindex(@code,@str,@indexb)
end

if @count=@no
begin

set @indexe=@intLen+charindex(@code,@str,@indexb)
if charindex(@code,@str,@indexe)<>0
return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
else
return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)

end

return ''

END

--- 用法

split('1;2;3;4;5;6',';',0) Return 1

split('1;2;3;4;5;6',';',5) Return 6

jeff_hua 2009-12-09