Single select join Sql query

Question

Using TableA with data provided below, I need a single SELECT query that returns the result in TableB:

TableA:

Id  Cvs 
--------------------------------
1   aaa,eee,ccc,ggg,hhh,bbb
2   ggg,bbb,ccc,ggg,aaa,bbb,bbb
3   ddd,ggg,eee

TableB:

Id  Value
1   aaa
1   eee
1   ccc
1   ggg
1   hhh
1   bbb
2   ggg
2   bbb
2   ccc
2   ggg
2   aaa
2   bbb
2   bbb
3   ddd
3   ggg
3   eee

Please help me how to do it, owing to T-SQL functions or join queries.

This might help: http://stackoverflow.com/questions/9811161/sql-server-split-csv-into-multiple-rows — Lasse Edsvik, Apr 24 '13 at 09:03

score 0 · Answer 1 · answered Apr 24 '13 at 09:24

Please try:

DECLARE @table as TABLE(ID int, CVS  Nvarchar(50))
insert into @table
select 1, 'aaa,eee,ccc,ggg,hhh,bbb' CVS union all
select 2, 'ggg,bbb,ccc,ggg,aaa,bbb,bbb' CVS union all
select 3, 'ddd,ggg,eee'

;WITH Split_Col 
AS
(
    SELECT ID, CONVERT(XML,'<table><col>' + ColName + '</col></table>') AS xmlcol
    FROM
    (
        SELECT ID, Split.a.value('.', 'VARCHAR(100)') AS ColName  
        FROM  
        (
             SELECT ID, CAST ('<M>' + REPLACE(CVS, ',', '</M><M>') + '</M>' AS XML) AS ColName  
             FROM  (select * from @table) TableName
         ) AS A 
            CROSS APPLY ColName.nodes ('/M') AS Split(a)
    ) TableName
)

 SELECT      
 ID, xmlcol.value('/table[1]/col[1]','varchar(100)') AS studentID
 FROM Split_Col 
 order by ID

score 0 · Answer 2 · answered Apr 24 '13 at 10:01

0

Try this

SELECT A.ID,  
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
FROM  
(
    SELECT ID,  
         CAST ('<M>' + REPLACE(CVS, ',', '</M><M>') + '</M>' AS XML) AS CVS  
    FROM  TableA 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

answered Apr 24 '13 at 10:01

bvr

4,786
1
20
24

score 0 · Answer 3 · answered Apr 24 '13 at 10:05

I would create a separate function that returns all values (including duplicated ones) from CSV list:

IF  EXISTS (SELECT * FROM sysobjects WHERE id  = OBJECT_ID(N'[dbo].[fn_TEST]') AND xtype  IN (N'FN', N'IF', N'TF'))
BEGIN
    DROP FUNCTION [dbo].[fn_TEST]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_TEST]
(
    @CSVList NVARCHAR(MAX)
)
RETURNS @ResultSet TABLE
(
     RecValue NVARCHAR(MAX)
    ,RecID BIGINT IDENTITY(1, 1) PRIMARY KEY
)
AS
BEGIN

    DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@CSVList, ',', ']]></r><r><![CDATA[') + ']]></r>'

    INSERT INTO @ResultSet (RecValue)
    SELECT RTRIM(LTRIM(Tbl.Col.value('.', 'nvarchar(max)')))
    FROM @xml.nodes('//r') Tbl(Col)

    RETURN
END

GO

Then you can simply use "SELECT" statement and "CROSS APPLY" statement like this:

SET NOCOUNT ON
GO

    DECLARE @DataSource AS TABLE
    (
         ID TINYINT IDENTITY(1,1)
        ,CSV NVARCHAR(4000)
    )

    INSERT INTO @DataSource ([CSV])
    VALUES ('aaa,eee,ccc,ggg,hhh,bbb')
          ,('ggg,bbb,ccc,ggg,aaa,bbb,bbb')
          ,('ddd,ggg,eee')

    SELECT ID
          ,RecValue
    FROM @DataSource DS
    CROSS APPLY [dbo].[fn_TEST](CSV) FN

SET NOCOUNT OFF
GO

score 0 · Answer 4 · answered Apr 24 '13 at 12:56

Please try one of these -

DECLARE @temp TABLE
(
      id INT
    , string NVARCHAR(200)
)

DECLARE @Separator CHAR(1)
SELECT @Separator = ','

INSERT INTO @temp (id, string)
VALUES 
(1,   'aaa,eee,ccc,ggg,hhh,bbb'),
(2,   'ggg,bbb,ccc,ggg,aaa,bbb,bbb'),
(3,   'ddd,ggg,eee')

1. XML

SELECT 
      id = p.value('(./n)[1]', 'INT')
    , name = p.value('(./s)[1]', 'NVARCHAR(200)')
FROM (
    SELECT field = CAST('<r><s>' + REPLACE(SUBSTRING(',' + t.string + ',', 2, LEN(',' + t.string + ',')), @Separator, '</s><n>' + CAST(t.id AS VARCHAR(10)) + '</n></r><r><s>') + '</s></r>' AS XML) 
    FROM @temp t
) d
CROSS APPLY field.nodes('/r') t(p)
WHERE t.p.exist('n') = 1

2. spt_values

SELECT 
      t.id
    , name = 
        SUBSTRING(
              ',' + t.string + ','
            , number + 1
            , CHARINDEX(@Separator, ',' + t.string + ',', number + 1) - number - 1)
FROM @temp t
CROSS JOIN [master].dbo.spt_values n
WHERE [type] = 'p'
    AND number <= LEN(',' + t.string + ',') - 1
    AND SUBSTRING(',' + t.string + ',', number, 1) = @Separator

3. WHILE

DECLARE @items TABLE 
(
      id INT
    , name NVARCHAR(50)
)

DECLARE
      @id INT
    , @string NVARCHAR(2000)
    , @pos INT

DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT id, string
    FROM @temp

OPEN cur

FETCH NEXT FROM cur INTO @id, @string

WHILE @@FETCH_STATUS = 0 BEGIN

    WHILE LEN(@string) > 0 BEGIN

        SELECT @pos = CHARINDEX(@Separator, @string, 1)

        IF @pos = 0
            SELECT @pos = LEN(@string)

        INSERT INTO @items (id, name) 
        SELECT @id, SUBSTRING(@string, 1, @pos - 1)

        SELECT @string = SUBSTRING(@string, @pos + 1, LEN(@string) - @pos)

    END

    FETCH NEXT FROM cur INTO @id, @string

END

CLOSE cur
DEALLOCATE cur

SELECT  
      i.id
    , i.name 
FROM @items i
WHERE name != ''

4. CTE

;WITH a AS
(
    SELECT 
          id
        , start_pos = 1
        , end_pos = CHARINDEX(@Separator, t.string)
        , t.string
    FROM @temp t

    UNION ALL

    SELECT 
          id
        , end_pos + 1
        , CHARINDEX(@Separator, string, end_pos + 1)
        , string
    FROM a
    WHERE end_pos > 0
)

SELECT 
      d.id
    , d.name 
FROM (
    SELECT 
          a.id
        , name = SUBSTRING(
              string
            , start_pos
            , ABS(end_pos - start_pos)
        ) 
    FROM a
) d
WHERE d.name != ''

Single select join Sql query

4 Answers4