关于2个表连接查询的疑难问题，高手请进！！

Question

table1:
ID, VALUE
01, XX|1
    YY|202, XX|1
    YY|3
    ZZ|5
...解释一下，这个VALUE是nvarchar类型：
XX|1
YY|2其中XX和1之间使用|符号分隔；YY, ZZ这两组也是一样。
组之间是以换行分隔，之所以写成这种形式是因为在asp里是有换行的。
table2:
ID, PID, NAME
1， 01，  XX
2,  01，  YY
3,  02,   XX
4,  02,   YY
5,  02,   ZZ
...table2.PID指向table1.ID；
table2.NAME指向table1.VALUE中其中一行的前半段(使用|符号分隔)我想要的结果是：得到对应数值的累加和，比如上例，我想得到：
PID, SUM(XX), SUM(YY), SUM(ZZ)
01,   1,        2,       0
02,   1,        3,       5不知道这样的功能可否实现？如果有困难，可以假设只有 XX,YY,ZZ 这3种情况。谢谢！

Answer 1

declare @t1 table ([ID] smallint,[VALUE] varchar(50))
insert into @t1
select '01','XX|1'+char(13)+'YY|2' union all
select '02','XX|1'+char(13)+'YY|3'+char(13)+'ZZ|5'declare @t2 table ([ID] int,[PID] varchar(2),[NAME] varchar(2))
insert into @t2
select 1,'01','XX' union all
select 2,'01','YY' union all
select 3,'02','XX' union all
select 4,'02','YY' union all
select 5,'02','ZZ'select
t.PID
,sum(case t.n when 'XX' then t.v else 0 end)[SUM(XX)]
,sum(case t.n when 'YY' then t.v else 0 end)[SUM(YY)]
,sum(case t.n when 'ZZ' then t.v else 0 end)[SUM(ZZ)]
from
(select
 t2.PID
,t2.NAME n
,substring(t1.Value
,(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)
,( case (charindex(char(13),t1.value,(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)))
when 0 then len(t1.value)+1
else (charindex(char(13),t1.value,(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)))
end)
-(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)
) v from @t2 t2 left join @t1 t1
on t2.PID = t1.ID
and charindex(t2.NAME,t1.VALUE)>0
)t
group by t.PIDPID  SUM(XX)     SUM(YY)     SUM(ZZ)
---- ----------- ----------- -----------
01   1           2           0
02   1           3           5

Answer 2

不仅仅有XX,YY,ZZ 时
create table t1 ([ID] smallint,[VALUE] varchar(50))
insert into t1
select '01','XX|1'+char(13)+'YY|2' union all
select '02','XX|1'+char(13)+'YY|3'+char(13)+'ZZ|5'
select '03','XX|1'+char(13)+'YY|3'+char(13)+'ZZ|5'+char(13)+'KK|50'create table t2  ([ID] int,[PID] varchar(2),[NAME] varchar(2))
insert into t2
select 1,'01','XX' union all
select 2,'01','YY' union all
select 3,'02','XX' union all
select 4,'02','YY' union all
select 5,'02','ZZ' union all
select 6,'03','XX' union all
select 7,'03','YY' union all
select 7,'03','ZZ' union all
select 8,'03','KK'declare @sql varchar(max)
set @sql = 'select t.PID '
select @sql=@sql + ',sum(case t.n when '''+n+''' then t.v else 0 end)[SUM(' + n + ')]'
from (select distinct NAME n from t2) t
set  @sql=@sql  + 'from
(select
 t2.PID
,t2.NAME n
,substring(t1.Value
,(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)
,( case (charindex(char(13),t1.value,(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)))
when 0 then len(t1.value)+1
else (charindex(char(13),t1.value,(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)))
end)
-(charindex(t2.NAME,t1.value)+len(t2.NAME)+1)
) v from t2 t2 left join t1 t1
on t2.PID = t1.ID
and charindex(t2.NAME,t1.VALUE)>0
)t
group by t.PID'exec(@sql)drop table t1,t2PID  SUM(KK)     SUM(XX)     SUM(YY)     SUM(ZZ)
---- ----------- ----------- ----------- -----------
01   0           1           2           0
02   0           1           3           5
03   0           0           0           0

Answer 3

感谢回复，XX,YY,ZZ可以固定(我的意思是三组)，但是它们的长度不固定(比如：XXX, YY, KKGG)，代码在我这里执行有问题。

Answer 4

我试了 没问题啊 sql2000的话 varchar(max)会有问题 改成varchar(8000)吧

Answer 5

我得到的错误信息是：Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Answer 6

谢谢大家回复！
我重新解释一下，简化一下难度：(整个数据库只有 XX, YYY, ZZZZ 这三组情况)
table1: 
ID, VALUE 
01, XX|1 
    YYY|2 02, XX|1 
    YYY|3 
    ZZZZ|5 
... 解释一下，这个VALUE是nvarchar类型： 
XX|1 
YYY|2 其中XX和1之间使用|符号分隔；YYY, ZZZZ这两组也是一样。 
组之间是以换行分隔，之所以写成这种形式是因为在asp里是有换行的。 
table2: 
ID, PID, NAME 
1， 01，  XX 
2,  01， YYY 
3,  02,  XX 
4,  02,  YYY 
5,  02,  ZZZZ 
... table2.PID指向table1.ID； 
table2.NAME指向table1.VALUE中其中一行的前半段(使用|符号分隔) 
我想要的结果是：得到对应数值的累加和，比如上例，我想得到： 
PID, SUM(XX), SUM(YYY), SUM(ZZZZ) 
01,  1,        2,      0 
02,  1,        3,      5 

Answer 7

你将楼上解决办法里面的YY改成YYY，zz改成ZZZZ不就可以了吗，相应修改一下

Answer 8

至少你的数据应该是:
table1: 
ID, VALUE 
01, XX|1 
01, YYY|2 02, XX|1 
02, YYY|3 
02, ZZZZ|5 你那种格式实在难搞.

Answer 9

gsk方案已经解决了大部分，只不过是如果改变了XX的长度（比如XXX）则出现问题。如果这个问题再次简化到只有XX,和YYY的情况，老大们请看一下有无解决方案？table1: 
ID, VALUE 
01, XX|1 
    YYY|2 02, XX|1 
    YYY|3 

Answer 10

请问这个问题的难点是在于换行呢？还是说XX与YYY的长度不等？