如何实现这样的行列转换。。

--参考此例:
表:
ID(自动增加的)     作者                             机构                                 刊名
1                             A,B,C                       D,E,F                         XX
2                             H,J,F,I                   Z,Y,W,V                     YY
依次类推,作者跟机构可能是2个,或者是3个,没规律
我想要实现的功能是:
作者                         机构                                         刊名
A                           D                                         XX
B                           E                                         XX
C                           F                                         XX
H                           Z                                         YY
J                           Y                                         YY
F                           W                                         YY
I                           V                                         YY
数据量大概是20W   条左右,我该怎么实现呢?
谢谢您!
CREATE TABLE tb(ID int,作者 varchar(10),机构 varchar(20),刊名 varchar(10))
INSERT into tb values(1,'A,B,C',  'D,E,F'  ,'XX')
INSERT into tb values(2,'H,J,F,I','ZZ,YY,WWW,VVVV','YY')
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b   select t1.作者,t2.机构,t1.刊名 from
(
  select px=(select count(1) from
  (
    SELECT A.ID, b.id bid,作者 = SUBSTRING(A.作者, B.ID, CHARINDEX(',', A.作者 + ',', B.ID) - B.ID),A.刊名 FROM tb A, tmp B WHERE SUBSTRING(',' + A.作者, B.id, 1) = ','
  ) m where id=t.id and bid<t.bid)+1 , * from
  (
    SELECT A.ID, b.id bid,作者 = SUBSTRING(A.作者, B.ID, CHARINDEX(',', A.作者 + ',', B.ID) - B.ID),A.刊名 FROM tb A, tmp B WHERE SUBSTRING(',' + A.作者, B.id, 1) = ','
  ) t
) t1,
(
  select px=(select count(1) from
  (
    SELECT A.ID, b.id bid,机构 = SUBSTRING(A.机构, B.ID, CHARINDEX(',', A.机构 + ',', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING(',' + A.机构, B.id, 1) = ','
  ) m where id=t.id and bid<t.bid)+1 , * from
  (
    SELECT A.ID, b.id bid,机构 = SUBSTRING(A.机构, B.ID, CHARINDEX(',', A.机构 + ',', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING(',' + A.机构, B.id, 1) = ','
  ) t
) t2
where t1.id = t2.id and t1.px = t2.px
order by t1.id , t1.pxDROP TABLE tb,tmp/*
作者         机构                   刊名
---------- -------------------- ----------
A          D                    XX
B          E                    XX
C          F                    XX
H          ZZ                   YY
J          YY                   YY
F          WWW                  YY
I          VVVV                 YY（所影响的行数为 7 行）
*/

CREATE TABLE tb(id int,n1 varchar(20),n2 varchar(20))
INSERT into tb values(1,'a¦b¦c','a_v¦b_v¦c_v')
INSERT into tb values(2,'b¦d'  ,'b_v¦d_v')
INSERT into tb values(3,'f¦d'  ,'f_v¦d_v')
go-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b   select id ,
  max(case n1 when 'a' then n2 else '' end) A,
  max(case n1 when 'b' then n2 else '' end) B,
  max(case n1 when 'c' then n2 else '' end) C,
  max(case n1 when 'd' then n2 else '' end) D,
  max(case n1 when 'f' then n2 else '' end) f
from
(
select t1.id ,t1.N1,t2.N2 from
(
  select px=(select count(1) from
  (
    SELECT A.ID, b.id bid,N1 = SUBSTRING(A.N1, B.ID, CHARINDEX('¦', A.N1 + '¦', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING('¦' + A.N1, B.id, 1) = '¦'
  ) m where id=t.id and bid<t.bid)+1 , * from
  (
    SELECT A.ID, b.id bid,N1 = SUBSTRING(A.N1, B.ID, CHARINDEX('¦', A.N1 + '¦', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING('¦' + A.N1, B.id, 1) = '¦'
  ) t
) t1,
(
  select px=(select count(1) from
  (
    SELECT A.ID, b.id bid,N2 = SUBSTRING(A.N2, B.ID, CHARINDEX('¦', A.N2 + '¦', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING('¦' + A.N2, B.id, 1) = '¦'
  ) m where id=t.id and bid<t.bid)+1 , * from
  (
    SELECT A.ID, b.id bid,N2 = SUBSTRING(A.N2, B.ID, CHARINDEX('¦', A.N2 + '¦', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING('¦' + A.N2, B.id, 1) = '¦'
  ) t
) t2
where t1.id = t2.id and t1.px = t2.px
) o
group by idDROP TABLE tb,tmp/*
id          A                    B                    C                    D                    f
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1           a_v                  b_v                  c_v
2                                b_v                                       d_v
3                                                                          d_v                  f_v（所影响的行数为 3 行）
*/

create table tt(id int identity ,n1 varchar(20),n2 varchar(20))
insert into tt select 'a ¦b ¦c',                     'a_v ¦b_v ¦c_v'
insert into tt select 'b ¦d',                     'b_v ¦d_v'
insert into tt select 'f ¦d',                     'f_v ¦d_v' select id=identity(int,1,1) into # from sysobjects
----静态的
select  max(case when left(substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id),1)='a' then substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id) else null end) as a,
max(case when left(substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id),1)='b' then substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id) else null end) as b,
max(case when left(substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id),1)='c' then substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id) else null end) as c,
max(case when left(substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id),1)='d' then substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id) else null end) as d,
max(case when left(substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id),1)='f' then substring(n2,b.id,charindex('¦',n2+'¦',b.id)-b.id) else null end) as f
from tt a,# b
where substring('¦'+n2,b.id,1)='¦'
group by a.id

max(case n1 when 'a' then n2 else '' end) A,
  max(case n1 when 'b' then n2 else '' end) B,
  max(case n1 when 'c' then n2 else '' end) C,
  max(case n1 when 'd' then n2 else '' end) D,
  max(case n1 when 'f' then n2 else '' end) f------------------------------------
为什么用max还有如果有n个直不是要写N个max????

实及情况表里记录id                           n1                             n2
1                           u/L ¦b/L ¦c/s                     0.1 ¦5.2 ¦2007-10-10
2                           b/r ¦d/r                           5.6 ¦7.5
3                           f/s ¦d/s                           5.6 ¦5.6 N1里的直不定的  大约是10个字段但是是由用户录入的

CREATE TABLE T
(
id INT,
n1 VARCHAR(50),
n2 VARCHAR(50)
)
INSERT INTO T
SELECT 1,'u/L   ¦b/L   ¦c/s','0.1   ¦5.2   ¦2007-10-10' UNION ALL
SELECT 2,'b/r   ¦d/r','5.6   ¦7.5' UNION ALL
SELECT 3,'f/s   ¦d/s','5.6   ¦5.6'
SELECT TOP 50 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000) SET @sql = ''
SELECT @sql = @sql+',['+s+'] = ISNULL(MAX(CASE WHEN S = '''+S+'''
                          THEN Value END),'''')'
  FROM
(
SELECT S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  FROM T A, # B
   WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
    GROUP BY SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  ) A
SET @sql = 'SELECT '+STUFF(@sql,1,1,'') EXEC(@sql+'
  FROM
(
SELECT T_ID=A.ID,B.ID,S=SUBSTRING(n1,b.id,CHARINDEX(''¦'',n1+''¦'',b.id)-b.id)
  FROM T A, # B
   WHERE B.id <= LEN(n1+''¦'') AND CHARINDEX(''¦'',''¦''+n1,b.id) =b.id
  ) A LEFT JOIN
(
SELECT B.ID,n1,Value=SUBSTRING(n2,b.id,CHARINDEX(''¦'',n2+''¦'',b.id)-b.id)
  FROM T A,# B
    WHERE B.id <=LEN(n2+''¦'') AND CHARINDEX(''¦'',''¦''+n2,b.id) = b.id
  ) B ON CHARINDEX(''¦''+REPLACE(A.S,'' '','''')+''¦'', ''¦''+REPLACE(b.n1,'' '','''')+''¦'')>0
   AND A.ID = B.ID GROUP BY A.T_ID ORDER BY A.T_ID')
DROP TABLE #
DROP TABLE T

b/L        b/r        c/s        d/r        d/s        f/s        u/L
---------- ---------- ---------- ---------- ---------- ---------- ----------
5.2                   2007-10-10                                  0.1
           5.6                   7.5
                                            5.6        5.6

--改了一下，但是情况只能针对，Ｎ１和Ｎ２中的元素个数相同的情况，如果不同的话
--比如N1:U/L   ¦b/L   ¦c/s      N2: 0.1   ¦5.2   那么还要自行修改CREATE TABLE T
(
id INT,
n1 VARCHAR(50),
n2 VARCHAR(50)
)
INSERT INTO T
SELECT 1,'u/L   ¦b/L   ¦c/s  ¦i/j','0.1   ¦5.2   ¦2007-10-10  ¦20   ' UNION ALL
SELECT 2,'b/r   ¦d/r','5.6   ¦7.5' UNION ALL
SELECT 3,'f/s   ¦d/s','5.6   ¦5.6'
SELECT TOP 50 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  INTO #1
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
   WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id)
  INTO #2
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
    WHERE B.id <=LEN(n2+'¦') AND CHARINDEX('¦','¦'+n2,b.id) = b.id
    ORDER BY A.ID,B.ID
SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
                          THEN Value END),'''') AS VARCHAR(10))'
  FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'') EXEC(@sql+'
  FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID ORDER BY A.ID')
DROP TABLE #,#1,#2
DROP TABLE T   b/L        b/r        c/s        d/r        d/s        f/s        i/j        u/L
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5.2                   2007-10-10                                  20         0.1
           5.6                   7.5
                                            5.6        5.6                   警告: 聚合或其它 SET 操作消除了空值。

呵呵！在网吧，不能测试，思路是这样的，先把N1分解到#1，然后把N2分解到#2，这里我偷懒了一下，因为考虑的是N1和N2一一对应的关系，如果不是，那么可以根据ID来生成一个排序！你可以把语句分解开一步一步执行，应该可以明白的！再说前面还有老龟的指点！：）

你加上ＸＬＯＣＫ来试一试呢？ SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  INTO #1
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T WITH(XLOCK,PAGLOCK)) A, # B
   WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id)
  INTO #2
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T WITH(XLOCK,PAGLOCK)) A, # B
    WHERE B.id <=LEN(n2+'¦') AND CHARINDEX('¦','¦'+n2,b.id) = b.id
    ORDER BY A.ID,B.ID

我使用了该存储
---exec(@sql) 得出了结果了我想把结果写进新表 ---exec( 'select * into tb from ( '+@sql+ ') ') 却提示子句order by报错 ?

EXEC(@sql+'
  FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID ORDER BY A.ID')
--这里不能用排序,因为在复合结果集中SET @sql = @sql + '
  FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID'SET @sql = 'SELECT * INTO tb FROM ('+@sql+') A'

Microsoft OLE DB Provider for SQL Server error '80040e14' 除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
这是什么错误？

如果去掉order by 好像结果就乱掉了不是正确的结果了！

CREATE TABLE T
(
id INT,
n1 VARCHAR(50),
n2 VARCHAR(50)
)
INSERT INTO T
SELECT 1,'u/L   ¦b/L   ¦c/s  ¦i/j','0.1   ¦5.2   ¦2007-10-10  ¦20   ' UNION ALL
SELECT 2,'b/r   ¦d/r','5.6   ¦7.5' UNION ALL
SELECT 3,'f/s   ¦d/s','5.6   ¦5.6'
SELECT TOP 50 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  INTO #1
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
   WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id)
  INTO #2
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
    WHERE B.id <=LEN(n2+'¦') AND CHARINDEX('¦','¦'+n2,b.id) = b.id
    ORDER BY A.ID,B.ID
SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
                          THEN Value END),'''') AS VARCHAR(10))'
  FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'') EXEC(@sql+' INTO TB
  FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID ORDER BY A.ID') SELECT * FROM TBDROP TABLE #,#1,#2,TB
DROP TABLE T   b/L        b/r        c/s        d/r        d/s        f/s        i/j        u/L
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5.2                   2007-10-10                                  20         0.1
           5.6                   7.5
                                            5.6        5.6                   （所影响的行数为 3 行）

SELECT TOP 8000 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(col,b.id,CHARINDEX('¦',col+'¦',b.id)-b.id)
  INTO #1
  FROM
    (SELECT top 100 ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj) A, # B
   WHERE B.id <= LEN(col+'¦') AND CHARINDEX('¦','¦'+col,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),col,Value=SUBSTRING(col_v,b.id,CHARINDEX('¦',col_v+'¦',b.id)-b.id)
  INTO #2
  FROM
    (SELECT top 100 ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj) A, # B
    WHERE B.id <=LEN(col_v+'¦') AND CHARINDEX('¦','¦'+col_v,b.id) = b.id
    ORDER BY A.ID,B.ID
SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
                          THEN Value END),'''') AS VARCHAR(100))'
  FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'') EXEC(@sql+' FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID ORDER BY A.ID')DROP TABLE #,#1,#2top 10 正常  100就出问题了  全部记录就不用说了！！

8306 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.6|34.2|61.4|237.2|3.64|8.4|25.4|3.32|3.26|59.6|10.1|99|0|02  1 2007 12:00AM|♂
8311 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.6|38.4|49.8|239.8|2.54|8|29.6|5.2|1.02|68|8.1|104|0|02  1 2007 12:00AM|♀
8329 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.6|40|72|351.8|2.56|2.4|32|2.56|1.14|72|10|122|0|02  9 2007 12:00AM|♂
8330 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.6|40.8|41.2|117.2|2.18|7.6|31.2|2.3|1.64|72|9.36|123|0|02  9 2007 12:00AM|♂
8335 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|37.6|53.6|168.8|1.76|8.8|24.8|2.44|1.88|62.4|7.64|128|0|01 16 2007 12:00AM|♀
8347 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|36.8|27.6|214|1.52|6.2|24.4|6.9|0.68|61.2|9.12|140|0|02 20 2007 12:00AM|♀
8350 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|37|48.6|200|2.02|9.2|25.4|6.16|0.8|62.4|10.18|143|0|02 20 2007 12:00AM|♀
8353 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3.2|37|42.2|114.4|1.76|10.2|23.6|3.28|0.74|60.6|8.76|146|0|02 11 2007 12:00AM|♂
8239 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 1.5|35|46.7|188.3|2.64|10.7|23.3|3.03|2.86|58.3|6.52|32|0|02  3 2007 12:00AM|♀
8242 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 1.3|30.3|53.1|207.4|1.63|9.8|22.7|2.44|1.62|53|6.05|35|0|02  3 2007 12:00AM|♀
8249 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 1.2|23.4|53.7|261.8|1.69|3.8|19|2.75|1.68|42.4|6.32|42|0|02 25 2007 12:00AM|♂
8254 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 1.2|17.5|43.8|278.9|0.58|6.1|14.2|1.2|0.62|31.7|3.67|47|0|02 25 2007 12:00AM|♀
8285 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|35.8|57.4|229|1.84|8|24.6|2.88|1.4|60.4|6.44|78|0|02 27 2007 12:00AM|♂
8291 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|35.6|66.8|244.8|1.34|8.4|23|2.78|0.74|58|5.96|84|0|02 27 2007 12:00AM|♀
-----------------------------------------------------------------
2.4 34.6 42.8 345.4 4 7.6 28.2 2.54 0.86 63.8 9.84 94 01 26 2007 12:00AM ♂
2.6 35.2 82 123.2 5.04 7.2 26.8 6.44 2.7 62 6.74 114 02 21 2007 12:00AM ♂
2.8 36.8 79.8 145.6 3.44 5.4 27.2 3.64 2.5 64 6.02 120 02 21 2007 12:00AM ♀
3.2 40 42.2 196 1.88 8.4 25.8 4.68 0.8 65.8 8.06 148 02 11 2007 12:00AM ♂
1.2 33.4 55.2 285.2 2.85 10.2 28.9 4.24 2.27 62.3 11.96 2 0 02 20 2007 12:00AM ♂
1.4 33.9 45.4 186.9 2.18 11.3 24.5 5.36 1.97 58.4 15.18 21 0 02 20 2007 12:00AM ♀
1.3 35.2 55.6 335.8 3.61 14.8 27.5 3.29 2.29 62.7 11.11 27 0 02  3 2007 12:00AM ♂
1 38.4 119 256.6 2.6 5.9 40.2 3.88 2.03 78.6 12.52 37 0 02 23 2007 12:00AM ♂
1.3 36.7 51.7 159.5 3.41 5.5 27.3 5.05 0.95 64.1 9.81 56 0 01 22 2007 12:00AM ♀
3 34 71.4 273.2 2.9 8.8 22.6 1.38 0.84 56.6 10.8 89 0 01 26 2007 12:00AM ♀
2.8 34 40.2 244.6 3.2 8 24.8 2.2 0.86 58.8 7.42 92 0 01 26 2007 12:00AM ♂
2.6 33.6 38.8 225.6 3.6 7.6 26.8 2.88 0.7 60.4 8.44 93 0 01 26 2007 12:00AM ♂

SELECT TOP 8000 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(col,b.id,CHARINDEX('|',col+'|',b.id)-b.id)
  INTO #1
  FROM
    (SELECT top 12 ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj) A, # B
   WHERE B.id <= LEN(col+'|') AND CHARINDEX('|','|'+col,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),col,Value=SUBSTRING(col_v,b.id,CHARINDEX('|',col_v+'|',b.id)-b.id)
  INTO #2
  FROM
    (SELECT top 12 ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj) A, # B
    WHERE B.id <=LEN(col_v+'|') AND CHARINDEX('|','|'+col_v,b.id) = b.id
    ORDER BY A.ID,B.ID
SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
                          THEN Value END),'''') AS VARCHAR(100))'
  FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'') EXEC(@sql+' FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID ORDER BY A.ID')DROP TABLE #,#1,#2-----------------------
加了top 12是正常的可我全部查询就错乱了
1.63 9.8 22.7 2.44 1.62 53 6.05 35 0 02  3 2007 12:00AM ♀ 1.4 34.8 67 303.2
2.61 6.8 26.5 2.96 0.61 61.9 10.25 50 0 01 22 2007 12:00AM ♂ 1.3 15 62 75.3
1.59 4.9 19 3.01 0.81 44 7.12 10 0 02 20 2007 12:00AM ♀ 1.5 31.1 38.2 254.6
2.84 6.6 20.4 1.46 0.74 54.8 10.44 86 0 02  5 2007 12:00AM ♀ 3 32.2 53.6 305.4
2.07 11.8 25.7 2.72 2.44 60.5 6.41 36 0 02  3 2007 12:00AM ♀ 1 38.4 119 256.6
1.7 12.4 21.2 6.16 1.24 52.3 10.49 11 20 02 20 2007 12:00AM ♀ 1.5 34.7 41.3 310.9
3.74 6.6 29.8 3.02 4.38 63.8 8.3 131 0 02 26 2007 12:00AM ♀ 2.2 32.4 61.2 144.8
2.9 5.8 24 5.42 2.4 59.8 6.7 119 0 02 21 2007 12:00AM ♀ 2.8 36.8 79.8 145.6
1.36 1.6 12 0.87 0.28 27 3.48 51 0 01 22 2007 12:00AM ♂ 1.2 21.7 70.6 132.5
2.68 7.2 26.6 5.1 2 60 7.16 74 0 02  8 2007 12:00AM ♂ 2.6 20.8 60.8 192.6
2.94 5.4 22.4 2.82 1.82 55 6.4 100 0 02  1 2007 12:00AM ♂ 2.6 35.4 49.8 199.8
1.76 2.4 26.6 4.72 0.82 65.8 12.3 139 0 02 20 2007 12:00AM ♀ 3 36.8 27.6 214
1.82 6.6 21.6 0.94 0.74 53.8 7.84 87 0 02  5 2007 12:00AM ♀ 3.2 34.2 40.6 294
1.56 4.6 16.6 4.18 1.28 47.6 6.8 75 0 02  8 2007 12:00AM ♂ 2.6 34.8 57.2 179.2
3.24 6.2 27.2 3.64 1.26 62 8.36 76 0 02 27 2007 12:00AM ♂ 2.6 25.8 58 141
2.14 5.8 21.6 1.5 0.9 55.8 6.84 88 0 01 26 2007 12:00AM ♀ 3 34 71.4 273.2
当我全部查询的时候  性别很明显看出错乱了我的想法是查出所有记录数 12  循环先12条插入然后第13-24插入依次循环下去！！
你的方法SELECT是不是查字符到8000到顶了？

CREATE TABLE T
(
ID INT,
N1 VARCHAR(200),
N2 VARCHAR(200)
)
INSERT INTO T
SELECT 1,'8306 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦34.2 ¦61.4 ¦237.2 ¦3.64 ¦8.4 ¦25.4 ¦3.32 ¦3.26 ¦59.6 ¦10.1 ¦99 ¦0 ¦02     1   2007   12:00AM ¦♂' UNION ALL
SELECT 2,'8311 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦38.4 ¦49.8 ¦239.8 ¦2.54 ¦8 ¦29.6 ¦5.2 ¦1.02 ¦68 ¦8.1 ¦104 ¦0 ¦02     1   2007   12:00AM ¦♀' UNION ALL
SELECT 3,'8329 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦40 ¦72 ¦351.8 ¦2.56 ¦2.4 ¦32 ¦2.56 ¦1.14 ¦72 ¦10 ¦122 ¦0 ¦02     9   2007   12:00AM ¦♂' UNION ALL
SELECT 4,'8330 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦40.8 ¦41.2 ¦117.2 ¦2.18 ¦7.6 ¦31.2 ¦2.3 ¦1.64 ¦72 ¦9.36 ¦123 ¦0 ¦02     9   2007   12:00AM ¦♂' UNION ALL
SELECT 5,'8335 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦37.6 ¦53.6 ¦168.8 ¦1.76 ¦8.8 ¦24.8 ¦2.44 ¦1.88 ¦62.4 ¦7.64 ¦128 ¦0 ¦01   16   2007   12:00AM ¦♀' UNION ALL
SELECT 6,'8347 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦36.8 ¦27.6 ¦214 ¦1.52 ¦6.2 ¦24.4 ¦6.9 ¦0.68 ¦61.2 ¦9.12 ¦140 ¦0 ¦02   20   2007   12:00AM ¦♀' UNION ALL
SELECT 7,'8350 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦37 ¦48.6 ¦200 ¦2.02 ¦9.2 ¦25.4 ¦6.16 ¦0.8 ¦62.4 ¦10.18 ¦143 ¦0 ¦02   20   2007   12:00AM ¦♀' UNION ALL
SELECT 8,'8353 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3.2 ¦37 ¦42.2 ¦114.4 ¦1.76 ¦10.2 ¦23.6 ¦3.28 ¦0.74 ¦60.6 ¦8.76 ¦146 ¦0 ¦02   11   2007   12:00AM ¦♂' UNION ALL
SELECT 9,'8239 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.5 ¦35 ¦46.7 ¦188.3 ¦2.64 ¦10.7 ¦23.3 ¦3.03 ¦2.86 ¦58.3 ¦6.52 ¦32 ¦0 ¦02     3   2007   12:00AM ¦♀' UNION ALL
SELECT 10,'8242 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.3 ¦30.3 ¦53.1 ¦207.4 ¦1.63 ¦9.8 ¦22.7 ¦2.44 ¦1.62 ¦53 ¦6.05 ¦35 ¦0 ¦02     3   2007   12:00AM ¦♀' UNION ALL
SELECT 11,'8249 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.2 ¦23.4 ¦53.7 ¦261.8 ¦1.69 ¦3.8 ¦19 ¦2.75 ¦1.68 ¦42.4 ¦6.32 ¦42 ¦0 ¦02   25   2007   12:00AM ¦♂' UNION ALL
SELECT 12,'8254 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.2 ¦17.5 ¦43.8 ¦278.9 ¦0.58 ¦6.1 ¦14.2 ¦1.2 ¦0.62 ¦31.7 ¦3.67 ¦47 ¦0 ¦02   25   2007   12:00AM ¦♀' UNION ALL
SELECT 13,'8285 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦35.8 ¦57.4 ¦229 ¦1.84 ¦8 ¦24.6 ¦2.88 ¦1.4 ¦60.4 ¦6.44 ¦78 ¦0 ¦02   27   2007   12:00AM ¦♂' UNION ALL
SELECT 14,'8291 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦35.6 ¦66.8 ¦244.8 ¦1.34 ¦8.4 ¦23 ¦2.78 ¦0.74 ¦58 ¦5.96 ¦84 ¦0 ¦02   27   2007   12:00AM ¦♀'   SELECT TOP 200 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  INTO #1
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
   WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id)
  INTO #2
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
    WHERE B.id <=LEN(n2+'¦') AND CHARINDEX('¦','¦'+n2,b.id) = b.id
    ORDER BY A.ID,B.ID
  SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
                          THEN Value END),'''') AS VARCHAR(10))'
  FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'') EXEC(@sql+' INTO TB
  FROM
  (SELECT T_ID = (SELECT COUNT(1) FROM #1 WHERE T_ID<=A.T_ID),ID,S FROM #1 A) A
  LEFT JOIN
  (SELECT T_ID = (SELECT COUNT(1) FROM #2 WHERE T_ID<=A.T_ID),n1,Value FROM #2 A) B
  ON A.T_ID = B.T_ID
  GROUP BY A.ID ORDER BY A.ID') SELECT * FROM TBDROP TABLE #,#1,#2,TB
DROP TABLE T

--这里又改了一下!
EXEC(@sql+' INTO TB
  FROM
  (SELECT T_ID = (SELECT COUNT(1) FROM #1 WHERE T_ID<=A.T_ID),ID,S FROM #1 A) A
  LEFT JOIN
  (SELECT T_ID = (SELECT COUNT(1) FROM #2 WHERE T_ID<=A.T_ID),n1,Value FROM #2 A) B
  ON A.T_ID = B.T_ID AND CHARINDEX(''¦''+REPLACE(A.S,'' '','''')+''¦'',''¦''+REPLACE(B.n1,'' '','''')+''¦'')>0
  GROUP BY A.ID ORDER BY A.ID')

CREATE TABLE T
(
ID INT,
N1 VARCHAR(200),
N2 VARCHAR(200)
)
INSERT INTO T
SELECT 1,'8306 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦34.2 ¦61.4 ¦237.2 ¦3.64 ¦8.4 ¦25.4 ¦3.32 ¦3.26 ¦59.6 ¦10.1 ¦99 ¦0 ¦02     1   2007   12:00AM ¦♂' UNION ALL
SELECT 2,'8311 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦38.4 ¦49.8 ¦239.8 ¦2.54 ¦8 ¦29.6 ¦5.2 ¦1.02 ¦68 ¦8.1 ¦104 ¦0 ¦02     1   2007   12:00AM ¦♀' UNION ALL
SELECT 3,'8329 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦40 ¦72 ¦351.8 ¦2.56 ¦2.4 ¦32 ¦2.56 ¦1.14 ¦72 ¦10 ¦122 ¦0 ¦02     9   2007   12:00AM ¦♂' UNION ALL
SELECT 4,'8330 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','2.6 ¦40.8 ¦41.2 ¦117.2 ¦2.18 ¦7.6 ¦31.2 ¦2.3 ¦1.64 ¦72 ¦9.36 ¦123 ¦0 ¦02     9   2007   12:00AM ¦♂' UNION ALL
SELECT 5,'8335 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦37.6 ¦53.6 ¦168.8 ¦1.76 ¦8.8 ¦24.8 ¦2.44 ¦1.88 ¦62.4 ¦7.64 ¦128 ¦0 ¦01   16   2007   12:00AM ¦♀' UNION ALL
SELECT 6,'8347 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦36.8 ¦27.6 ¦214 ¦1.52 ¦6.2 ¦24.4 ¦6.9 ¦0.68 ¦61.2 ¦9.12 ¦140 ¦0 ¦02   20   2007   12:00AM ¦♀' UNION ALL
SELECT 7,'8350 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦37 ¦48.6 ¦200 ¦2.02 ¦9.2 ¦25.4 ¦6.16 ¦0.8 ¦62.4 ¦10.18 ¦143 ¦0 ¦02   20   2007   12:00AM ¦♀' UNION ALL
SELECT 8,'8353 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3.2 ¦37 ¦42.2 ¦114.4 ¦1.76 ¦10.2 ¦23.6 ¦3.28 ¦0.74 ¦60.6 ¦8.76 ¦146 ¦0 ¦02   11   2007   12:00AM ¦♂' UNION ALL
SELECT 9,'8239 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.5 ¦35 ¦46.7 ¦188.3 ¦2.64 ¦10.7 ¦23.3 ¦3.03 ¦2.86 ¦58.3 ¦6.52 ¦32 ¦0 ¦02     3   2007   12:00AM ¦♀' UNION ALL
SELECT 10,'8242 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.3 ¦30.3 ¦53.1 ¦207.4 ¦1.63 ¦9.8 ¦22.7 ¦2.44 ¦1.62 ¦53 ¦6.05 ¦35 ¦0 ¦02     3   2007   12:00AM ¦♀' UNION ALL
SELECT 11,'8249 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.2 ¦23.4 ¦53.7 ¦261.8 ¦1.69 ¦3.8 ¦19 ¦2.75 ¦1.68 ¦42.4 ¦6.32 ¦42 ¦0 ¦02   25   2007   12:00AM ¦♂' UNION ALL
SELECT 12,'8254 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','1.2 ¦17.5 ¦43.8 ¦278.9 ¦0.58 ¦6.1 ¦14.2 ¦1.2 ¦0.62 ¦31.7 ¦3.67 ¦47 ¦0 ¦02   25   2007   12:00AM ¦♀' UNION ALL
SELECT 13,'8285 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦35.8 ¦57.4 ¦229 ¦1.84 ¦8 ¦24.6 ¦2.88 ¦1.4 ¦60.4 ¦6.44 ¦78 ¦0 ¦02   27   2007   12:00AM ¦♂' UNION ALL
SELECT 14,'8291 A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别','3 ¦35.6 ¦66.8 ¦244.8 ¦1.34 ¦8.4 ¦23 ¦2.78 ¦0.74 ¦58 ¦5.96 ¦84 ¦0 ¦02   27   2007   12:00AM ¦♀'
这个多插入几条然后相应的去掉几列如性别列去掉插入请用原来的方测试一下

SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  INTO #1
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
   WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id)
  INTO #2
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
    WHERE B.id <=LEN(n2+'¦') AND CHARINDEX('¦','¦'+n2,b.id) = b.id
    ORDER BY A.ID,B.ID --在这里加个语句看看对应关系
  select * from #1 A INNER JOIN #2 B ON A.T_ID = B.T_ID

你把所有的数据来测试一下,看看#1,#2的条数,然后查查是否对应,如果对应的话,用
EXEC(@sql+' INTO TB
FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
GROUP BY A.ID ORDER BY A.ID')
应该是没有问题的

LEFT也可以改成INNER的

我猜想应该是出现了不太协调的数据,导致#1的T_ID和#2的T_ID错位的缘故.

9808 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 1.2|36.7|44.6|142.3|2.76|11.5|29.9|5.32|1.65|66.6|11.1|17|0|02 20 2007 12:00AM|♂
9846 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 1.5|13.8|60.4|89.3|1.02|1.4|9.3|0.94|0.33|23.1|2.19|55|0|01 22 2007 12:00AM|♀
9850 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 1.4|19.3|51.4|165.9|1.67|1.2|14|2.26|0.71|33.3|4.14|59|0|02  2 2007 12:00AM|♀
9873 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.8|34.2|53|139.4|1.82|10.2|23.6|1.84|2.34|57.8|4.82|82|0|02 27 2007 12:00AM|♀
9879 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3.2|34.2|40.6|294|2.14|5.8|21.6|1.5|0.9|55.8|6.84|88|0|01 26 2007 12:00AM|♀
9880 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|34|71.4|273.2|2.9|8.8|22.6|1.38|0.84|56.6|10.8|89|0|01 26 2007 12:00AM|♀
9893 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.6|37.6|58.6|253|2.76|7.6|28|5.64|0.96|65.6|10.78|102|0|02  1 2007 12:00AM|♂
9898 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.2|25.6|77|220.8|2.72|5.8|22.8|5.98|0.58|48.4|11.86|107|0|02  1 2007 12:00AM|♀
9901 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.2|36.6|81.8|193.2|5.24|2.6|32.6|7.76|1.72|69.2|11.12|110|0|01 29 2007 12:00AM|♂
9903 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.8|34|60|141.4|4.14|6.2|24.8|8.08|1.52|58.8|6.56|112|0|02 21 2007 12:00AM|♂
9917 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|36.4|43.4|142.6|2.54|5.4|24.2|2.34|1.58|60.8|10.76|126|0|02  9 2007 12:00AM|♂
9923 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 2.2|32.4|61.2|144.8|3.48|11|29.4|4.18|6.34|61.8|8.78|132|0|02 26 2007 12:00AM|♀
9946 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|测试|出生日期|性别 3|37.2|52.6|227.2|2.1|12.8|25.96|5.54|1.68|62.8|10.62|155|0|02 12 2007 12:00AM|♀
9959 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.5|34.7|41.3|310.9|2.07|13|23.9|4.79|1.36|58.6|9.31|12|02 20 2007 12:00AM|♀
9963 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.3|22.7|36|135.7|1.57|6.1|17.4|2.69|0.64|40.1|7.41|16|02 20 2007 12:00AM|♂
9966 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.4|35.2|43.3|196.3|1.94|1|24.8|0.07|1.38|60|9.96|19|02 20 2007 12:00AM|♀
9967 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.5|33.9|43.9|133|1.92|12|22.9|5.59|1.53|56.8|16.28|20|02 20 2007 12:00AM|♀
9974 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.3|35.2|55.6|335.8|3.61|14.8|27.5|3.29|2.29|62.7|11.11|27|02  3 2007 12:00AM|♂
9982 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.3|30.3|53.1|207.4|1.63|9.8|22.7|2.44|1.62|53|6.05|35|02  3 2007 12:00AM|♀
9994 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.2|17.5|43.8|278.9|0.58|6.1|14.2|1.2|0.62|31.7|3.67|47|02 25 2007 12:00AM|♀
9997 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.3|35.4|89.9|169.6|2.61|6.8|26.5|2.96|0.61|61.9|10.25|50|01 22 2007 12:00AM|♂
10009 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 0.9|34|68|114.7|0.77|0|18.5|0|0.37|35.5|0|62|01 12 2007 12:00AM|♂
10016 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 1.2|35|47.3|134.8|3.21|0|28.1|0.01|3.35|63.1|4.52|69|02 14 2007 12:00AM|♀
10017 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 0|0|74.6|165|1|0|0|0.04|0.03|0|0|70|02 14 2007 12:00AM|♀
10030 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 2.8|37.4|65.8|219.4|1.26|5.8|27.4|1.64|0.92|64.8|7.54|83|02 27 2007 12:00AM|♀
10040 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 2.6|33.6|38.8|225.6|3.6|7.6|26.8|2.88|0.7|60.4|8.44|93|01 26 2007 12:00AM|♂
10041 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 2.4|34.6|42.8|345.4|4|7.6|28.2|2.54|0.86|63.8|9.84|94|01 26 2007 12:00AM|♂
10046 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 2.6|34.2|61.4|237.2|3.64|8.4|25.4|3.32|3.26|59.6|10.1|99|02  1 2007 12:00AM|♂
10047 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 3|32.6|54.8|180.8|2.94|5.4|22.4|2.82|1.82|55|6.4|100|02  1 2007 12:00AM|♂
10050 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 2.8|30.2|50.2|217|2.82|13.2|21.6|4.7|0.9|51.8|14.42|103|02  1 2007 12:00AM|♀
10068 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 2.8|35.6|63.4|221.6|2.4|4|26|2.34|0.9|61.6|7.76|121|02  9 2007 12:00AM|♂
10069 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 2.6|40|72|351.8|2.56|2.4|32|2.56|1.14|72|10|122|02  9 2007 12:00AM|♂
10075 A/G|ALB(g/L)|ALT(U/L)|AST(U/L)|CHOL(mmol/L)|Cr(umol/L)|G(g/L)|Glu(mmol/L)|TG(mmol/L)|TP(g/L)|Urea(mmol/L)|编号|出生日期|性别 3|37.6|53.6|168.8|1.76|8.8|24.8|2.44|1.88|62.4|7.64|128|01 16 2007 12:00AM|♀

A/G ¦ALB(g/L) ¦ALT(U/L) ¦AST(U/L) ¦CHOL(mmol/L) ¦Cr(umol/L) ¦G(g/L) ¦Glu(mmol/L) ¦TG(mmol/L) ¦TP(g/L) ¦Urea(mmol/L) ¦编号 ¦测试 ¦出生日期 ¦性别加入查出的是正确使用上面的存储
那么我再传递条件如何传递
或者以下面的例子
CREATE TABLE T
(
id INT,
n1 VARCHAR(50),
n2 VARCHAR(50)
)
INSERT INTO T
SELECT 1,'u/L   ¦b/L   ¦c/s  ¦i/j','0.1   ¦5.2   ¦2007-10-10  ¦20   ' UNION ALL
SELECT 2,'b/r   ¦d/r','5.6   ¦7.5' UNION ALL
SELECT 3,'f/s   ¦d/s','5.6   ¦5.6'
SELECT TOP 50 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
  INTO #1
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
   WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
   ORDER BY A.ID,B.ID
SELECT T_ID =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id)
  INTO #2
  FROM
    (SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
    WHERE B.id <=LEN(n2+'¦') AND CHARINDEX('¦','¦'+n2,b.id) = b.id
    ORDER BY A.ID,B.ID
SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
                          THEN Value END),'''') AS VARCHAR(10))'
  FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'') EXEC(@sql+'
  FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID ORDER BY A.ID')
DROP TABLE #,#1,#2
DROP TABLE T   b/L        b/r        c/s        d/r        d/s        f/s        i/j        u/L
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5.2                   2007-10-10                                  20         0.1
           5.6                   7.5
                                            5.6        5.6                   我传递了 b/L=5.2,b/r='' order by b/L
存储过程分别传递了 @cxtj  b/L=5.2,b/r=''
@cxpx   order by b/L
我怎么组合到EXEC(@sql+'
  FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
    GROUP BY A.ID ORDER BY A.ID')
这上面阿？？

调试易

如何实现这样的行列转换。。

解决方案 »