Association between varchar type field queries

Suppose there are A, B two tables
A table field: ID, BID
B table field: ID, Name

The BID field of the A table is of type varchar, one or more ID preservation is the B table, comma separated. For example: 1,2,3

Now need to find corresponding table according to the BID B Name, could you tell me how to query?


For example
The A table has 2 records
ID BID
1 1, 2, 3
2 1

The B table has 3 records
ID Name
1 b1
2 b2
3 b3


How to get results:
ID BName BID
1 b1,b2,b3 1,2,3
2 b1 1

Started by Page at November 19, 2016 - 8:38 PM

----------------------------------------------------------------
Author: DBA_Huangzj (poop all over the wall)
-- Date    :2013-11-06 10:21:13
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> Test data: [A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[BID] varchar(5))
insert [A]
select 1,'1,2,3' union all
select 2,'1'
--> Test data: [B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[Name] varchar(2))
insert [B]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
- start query --------------------------


;
WITH    cte
          AS ( SELECT   a.id ,
                        b.NAME ,
                        bid
               FROM     ( SELECT    id ,
                                    SUBSTRING([BID], number,
                                              CHARINDEX(',', [BID] + ',',
                                                        number) - number) AS [BID]
                          FROM      [A] a ,
                                    master..spt_values
                          WHERE     number >= 1
                                    AND number <= LEN([BID])
                                    AND type = 'p'
                                    AND SUBSTRING(',' + [BID], number, 1) = ','
                        ) a
                        INNER JOIN [b] ON a.bid = b.id
             )
    SELECT  a.id ,
            STUFF(( SELECT  ',' + NAME
                    FROM    cte b
                    WHERE   b.id = a.id
                  FOR
                    XML PATH('')
                  ), 1, 1, '') 'BNAME' ,
            STUFF(( SELECT  ',' + CONVERT(VARCHAR(10), bid)
                    FROM    cte b
                    WHERE   b.id = a.id
                  FOR
                    XML PATH('')
                  ), 1, 1, '') 'bid'
    FROM    cte a
    GROUP BY a.id
/* 
The results ---------------------------- situation
id          BNAME                                                                                                                                                                                                                                                            bid
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           b1,b2,b3                                                                                                                                                                                                                                                         1,2,3
2           b1        
*/

Posted by Jeff at November 20, 2016 - 9:11 PM


if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[BID] varchar(5))
insert [A]
select 1,'1,2,3' union all
select 2,'1'
--> Test data: [B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[Name] varchar(2))
insert [B]
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'


;with t
as
(
select a.id,b.id as bid,b.name as bname
from a
inner join b
        on charindex(','+cast(b.id as varchar)+',',','+bid+',')>0
)


select distinct
       id,
       
       stuff(
              (
                select ','+cast(t2.bid as varchar) from t t2 where t1.id = t2.id for xml path('')
              ),
              1,1,''
            ) as bid,
            
       stuff(
              (
                select ','+t2.bname from t t2 where t1.id = t2.id for xml path('')
              ),
              1,1,''
            ) as bname
from t t1
/*
id	bid	    bname
1	1,2,3	b1,b2,b3
2	1	    b1
*/

Posted by Lyndon at November 30, 2016 - 9:57 PM

- write too complex. . . . 
IF OBJECT_ID('tempdb..#ats_test','U') IS NOT NULL DROP TABLE #ats_test
CREATE TABLE #ats_test
  (
     ID	INT
     ,BID VARCHAR(20)
  ) 

insert into #ats_test values (1,     '1,2,3')
insert into #ats_test values (2,'1')

IF OBJECT_ID('tempdb..#TempA','U') IS NOT NULL DROP TABLE #TempA
CREATE TABLE #TempA(
	ID	INT
	,BID INT

)

IF OBJECT_ID('tempdb..#TempB','U') IS NOT NULL DROP TABLE #TempB
CREATE TABLE #TempB(
	ID	INT
	,Name	VARCHAR(10)

)
INSERT INTO #TempB 
SELECT 1,     'b1' UNION ALL
SELECT 2,     'b2' UNION ALL
SELECT 3,     'b3' 

DECLARE @Cnt INT=(SELECT Max(Id) FROM #ats_test)
DECLARE @Txt NVARCHAR(10)='',@Num NVARCHAR(10)=''
DECLARE @Index INT
WHILE @Cnt>0
BEGIN
	SELECT @Txt=BID FROM #ats_test WHERE Id=@Cnt
	WHILE LEN(@Txt)>0
		BEGIN
			IF CHARINDEX(',',@Txt)>0
			BEGIN
				SET @Index=CHARINDEX(',',@Txt)
				SET @Num=LEFT(@Txt,@Index-1)
				INSERT INTO #TempA(ID,BID) VALUES(@Cnt,CAST(@Num AS INT))
				SET @Txt=STUFF(@Txt,1,CHARINDEX(',',@Txt),'')
				
			END  
			ELSE 
            BEGIN
              INSERT INTO #TempA(ID,BID) VALUES(@Cnt,CAST(@Txt AS INT))
            SET @Txt=STUFF(@Txt,1,LEN(@Txt),'')
                 
            END
		END
	SET @Cnt=@Cnt-1
END

;WITH CTE 
AS (
SELECT A.ID
		,A.BID
		,B.Name 
	FROM #TempA AS A 
	JOIN #TempB AS B ON A.BID=B.ID 
)
SELECT A.ID
	,BName=STUFF((SELECT ','+T.Name FROM CTE AS T WHERE A.ID=T.ID FOR XML PATH('')),1,1,'')
	,BID=STUFF((SELECT ','+CAST(T1.BID AS VARCHAR) FROM CTE AS T1 WHERE A.ID=T1.ID FOR XML PATH('')),1,1,'')
FROM CTE AS A
GROUP BY A.ID
	

Posted by June at December 13, 2016 - 10:26 PM