如何查询逗号隔开的列

Question

有两张表其中表A里的数据：
col1   col2   col3
1       张三   1,2,3
2       李四   2,3
3       王五   2表A里的col3里存放的是表B的主键 逗号隔开
表B里的数据：
col1     col2
1        数学
2        英语
3        语文
如何对表A进行查询得结果为
col1   col2   col3
1       张三   数学,英语,语文
2       李四   英语,语文
3       王五   英语本想写一个函数的，可是函数里不允许使用exec
求高人指点

Answer 1

你参考一下，改一下就可以拆分了declare @s varchar(10)
 set @s='4~5~6'
 
 select 
 parsename(replace(@s,'~','.'),3) as 第一个位置,
 parsename(replace(@s,'~','.'),2) as 第二个位置,
 parsename(replace(@s,'~','.'),1) as 第三个位置
 
 /*
 第一个位置     第二个位置           第三个位置
 -------------- -------------------  -------------------
 4              5                    6
 */
 

Answer 2

A表里的col3列里的数据有可能是很多的  parsename不合适

Answer 3

--> 测试数据：#ta
IF OBJECT_ID('TEMPDB.DBO.#ta') IS NOT NULL DROP TABLE #ta
GO 
CREATE TABLE #ta([col1] INT,[col2] VARCHAR(4),[col3] VARCHAR(5))
INSERT #ta
SELECT 1,'张三','1,2,3' UNION ALL
SELECT 2,'李四','2,3' UNION ALL
SELECT 3,'王五','2'--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO 
CREATE TABLE #tb([col1] INT,[col2] VARCHAR(4))
INSERT #tb
SELECT 1,'数学' UNION ALL
SELECT 2,'英语' UNION ALL
SELECT 3,'语文'
--------------开始查询--------------------------
;WITH cte AS (
SELECT a.[col1],a.[col2],[col3]=T.c.value('.', 'int') FROM 
(
SELECT [col1],[col2],[col3]=CAST('<x>'+REPLACE([col3],',','</x><x>')+'</x>' AS XML)FROM #ta 
) AS a
CROSS APPLY  a.col3.nodes('/x/text()') T(c)
)
SELECT t.[col1],t.[col2],STUFF((SELECT ','+b.[col2] FROM cte c JOIN #tb b ON  b.[col1]=c.[col3] WHERE c.[col1]=t.[col1] FOR XML PATH('')),1,1,'')
FROM cte t
GROUP BY t.[col1],t.[col2]
----------------结果----------------------------
/* 
col1 col2 (无列名)
2 李四 英语,语文
3 王五 英语
1 张三 数学,英语,语文
*/

Answer 4

你顶多写成存储过程，把表名、列名参数化。
因为写成函数的时候，不能把表名，列名作为参数，当把他们作为参数的时候
就必须使用动态sql 语句 就是需要 exec 去执行，这个是函数不允许的

Answer 5

if(object_id('a') is not null) drop table a
create table a
(
col1 int,
col2 varchar(80),
col3 varchar(4000)
)
insert into a
select 1,'张三','1,2,3' union all
select 2,'李四','2,3' union all
select 3,'王五','2'if(object_id('b') is not null) drop table b
create table b
(
col1 int,
col2 varchar(50)
)insert into b
select 1,'数学' union all
select 2,'英语' union all
select 3,'语文'
select * from a 
select * from balter procedure Getcol3
as
set nocount on
declare @col1 int
declare @col2 varchar(50)
declare @col3 varchar(4000)
declare @colName varchar(4000)
declare @sql varchar(4000)
create table #t
(
col1 int,
col2 varchar(80),
col3 varchar(4000)
)
declare getcolCur cursor for select col1,col2,col3 from a
open getcolCur
fetch next from GetcolCur into @col1,@col2,@col3
while(@@fetch_status=0)
 begin
   set @colName =''
   if(len(replace(@col3,',',''))>0)
    begin
   while (len(replace(@col3,',',''))>0)
    begin
            print'循环开始'
            set @sql = len(replace(@col3,',',''))
            print @sql
      
      set @colName = @colName+(select col2 from b where col1 = left(replace(@col3,',',''),1))+','
              print @colName
      set @col3 = right(replace(@col3,',',''),len(replace(@col3,',',''))-1)
              print @col3
             print '循环结束'
    end--end len
       set @colName = left(@colName,len(@colName)-1)
      insert into #t
      select @col1,@col2,@colName
      if(@@error<>0)
       begin 
        print @col1
        print @col2
        print @colName
       end--end error
    end--end if
    fetch next from getcolcur into @col1,@col2,@col3
 end--end @@fetch
 select * from #t
close getcolCur
deallocate getcolCur
drop table #t
go--测试
exec getcol3
/*
1 张三 数学,英语,语文
2 李四 英语,语文
3 王五 英语*/

Answer 6

if(object_id('a') is not null) drop table a
create table a
(
col1 int,
col2 varchar(80),
col3 varchar(4000)
)
insert into a
select 1,'张三','1,2,3' union all
select 2,'李四','2,3' union all
select 3,'王五','2'if(object_id('b') is not null) drop table b
create table b
(
col1 int,
col2 varchar(50)
)insert into b
select 1,'数学' union all
select 2,'英语' union all
select 3,'语文'
select * from a 
select * from balter procedure Getcol3
as
set nocount on
declare @col1 int
declare @col2 varchar(50)
declare @col3 varchar(4000)
declare @colName varchar(4000)
create table #t
(
col1 int,
col2 varchar(80),
col3 varchar(4000)
)
declare getcolCur cursor for select col1,col2,col3 from a
open getcolCur
fetch next from GetcolCur into @col1,@col2,@col3
while(@@fetch_status=0)
 begin
   set @colName =''
   if(len(replace(@col3,',',''))>0)
    begin
   while (len(replace(@col3,',',''))>0)
    begin       
      set @colName = @colName+(select col2 from b where col1 = left(replace(@col3,',',''),1))+','
              print @colName
      set @col3 = right(replace(@col3,',',''),len(replace(@col3,',',''))-1)
    end--end len
       set @colName = left(@colName,len(@colName)-1)
      insert into #t
      select @col1,@col2,@colName
      if(@@error<>0)
       begin 
        print @col1
        print @col2
        print @colName
       end--end error
    end--end if
    fetch next from getcolcur into @col1,@col2,@col3
 end--end @@fetch
 select * from #t
close getcolCur
deallocate getcolCur
drop table #t
go--测试
exec getcol3
/*
1 张三 数学,英语,语文
2 李四 英语,语文
3 王五 英语*/

Answer 7

---擦这次没问题了。居然是alter procedure...
if(object_id('a') is not null) drop table a
create table a
(
col1 int,
col2 varchar(80),
col3 varchar(4000)
)
insert into a
select 1,'张三','1,2,3' union all
select 2,'李四','2,3' union all
select 3,'王五','2'if(object_id('b') is not null) drop table b
create table b
(
col1 int,
col2 varchar(50)
)insert into b
select 1,'数学' union all
select 2,'英语' union all
select 3,'语文'
select * from a 
select * from bcreate procedure Getcol3
as
set nocount on
declare @col1 int
declare @col2 varchar(50)
declare @col3 varchar(4000)
declare @colName varchar(4000)
create table #t
(
col1 int,
col2 varchar(80),
col3 varchar(4000)
)
declare getcolCur cursor for select col1,col2,col3 from a
open getcolCur
fetch next from GetcolCur into @col1,@col2,@col3
while(@@fetch_status=0)
 begin
   set @colName =''
   if(len(replace(@col3,',',''))>0)
    begin
       while (len(replace(@col3,',',''))>0)
        begin          
          set @colName = @colName+(select col2 from b where col1 = left(replace(@col3,',',''),1))+','
              print @colName
          set @col3 = right(replace(@col3,',',''),len(replace(@col3,',',''))-1)
        end--end len
       set @colName = left(@colName,len(@colName)-1)
      insert into #t
      select @col1,@col2,@colName
      if(@@error<>0)
       begin 
        print @col1
        print @col2
        print @colName
       end--end error
    end--end if
    fetch next from getcolcur into @col1,@col2,@col3
 end--end @@fetch
 select * from #t
close getcolCur
deallocate getcolCur
drop table #t
go--测试
exec getcol3
/*
1    张三    数学,英语,语文
2    李四    英语,语文
3    王五    英语*/

Answer 8

--创建测试数据
IF OBJECT_ID('DBO.T1') IS NOT NULL DROP TABLE DBO.T1
GO 
CREATE TABLE DBO.T1([col1] INT,[col2] VARCHAR(4),[col3] VARCHAR(5))
go
INSERT  DBO.T1
SELECT 1,'张三','1,2,3' UNION ALL
SELECT 2,'李四','2,3' UNION ALL
SELECT 3,'王五','2'UNION ALL
SELECT 4,'赵六','1,2'IF OBJECT_ID('DBO.T2') IS NOT NULL DROP TABLE DBO.T2
GO 
CREATE TABLE DBO.T2([col1] INT,[col2] VARCHAR(4))
INSERT DBO.T2
SELECT 1,'数学' UNION ALL
SELECT 2,'英语' UNION ALL
SELECT 3,'语文'--创建一个将以逗号为分隔符的字符转换为表的函数
if(OBJECT_ID('f_splitstr') is not null) drop function f_splitstr
go
create function f_splitstr(@str varchar(8000)  
)
RETURNS @t TABLE(id int)  
AS
BEGIN
    DECLARE @pos int
    SET @pos = CHARINDEX(',',@str)  
    WHILE @pos > 0  
    BEGIN
        INSERT @t(id) VALUES(LEFT(@str,@pos- 1))  
        SELECT
            @str= STUFF(@str,1,@pos,''),  
            @pos= CHARINDEX(',',@str)  
    END
    IF len(@str)>0
        INSERT @t(id) VALUES(@str)  
    RETURN  
ENDgo
select col1,col2,STUFF((select ','+t2.col2 from DBO.T2 as t2 where col1 in(select id from f_splitstr(t1.col3)) for XML path('')),1,1,'')as col3 from DBO.T1 as t1
/* 结果
col1 col2 col3  
1  张三  数学,英语,语文
2  李四  英语,语文
3  王五  英语
4  赵六  数学,英语 
*/