tgids 里面装的是一个Id组，用空格隔开，和tgvalues里面ID 是一一对应的
id tgids tgvalues
1 1 2 3 7 8 9
2 3 5 9 11
能否把里面值取出，显示如下
id tgid tgvalue
1 1 7
1 2 8
1 3 9
2 3 9
2 5 11
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-29 13:11:21
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[tgids] VARCHAR(10),[tgvalues] VARCHAR(10))
insert [huang]
select 1,'1 2 3' ,'7 8 9' union all
select 2,'3 5', '9 11'
--------------生成数据--------------------------
SELECT id ,
SUBSTRING([tgids], number,
CHARINDEX(',', [tgids] + ',', number) - number) AS [tgids] ,
SUBSTRING([tgvalues], number,
CHARINDEX(',', [tgvalues] + ',', number) - number) AS [tgvalues]
FROM ( SELECT id ,
REPLACE([tgids], ' ', ',') [tgids] ,
REPLACE([tgvalues], ' ', ',') [tgvalues]
FROM [huang]
) a ,
master..spt_values
WHERE number >= 1
AND number <= LEN([tgids])
AND type = 'p'
AND SUBSTRING(',' + [tgids], number, 1) = ','
----------------结果----------------------------