CODEWITHSUNDEEP
sqlsql-serverconcatenation
xyz
xyz

Reputation: 782

concat a column fields of a table group by the fields of another table

I have two tables say t1 and t2 which join with testid.I need to concat val column of t2 if d1,d2 and id of table t1 is same.Please refer to the following tables.

    CREATE TABLE #t1 (d1 varchar(100),d2 varchar(100),id int,testid int)

    INSERT INTO #t1 (d1,d2,id,testid) VALUES ('p','q',1,101)
    INSERT INTO #t1 (d1,d2,id,testid) VALUES ('r','s',2,102)
    INSERT INTO #t1 (d1,d2,id,testid) VALUES ('p','q',1,103)
    INSERT INTO #t1 (d1,d2,id,testid) VALUES ('r','s',1,104)

    CREATE TABLE #t2 (testid int,val varchar(100))

    INSERT INTO #t2 (testid,val) values (101,'x')
    INSERT INTO #t2 (testid,val) values (102,'y')
    INSERT INTO #t2 (testid,val) values (103,'z')
    INSERT INTO #t2 (testid,val) values (104,'xx')

The result should be:

    d1 d2 pid val
    p  q  1   x,z
    r  s  2   y
    r  s  1   xx

Upvotes: 1

Views: 54

Answers (1)

Devart
Devart

Reputation: 121902

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
    DROP TABLE #t1

IF OBJECT_ID('tempdb.dbo.#t2') IS NOT NULL
    DROP TABLE #t2

CREATE TABLE #t1 (d1 VARCHAR(100), d2 VARCHAR(100), id INT, testid INT)
INSERT INTO #t1 (d1, d2, id, testid)
VALUES ('p', 'q', 1, 101)
     , ('r', 's', 2, 102)
     , ('p', 'q', 1, 103)
     , ('r', 's', 1, 104)

CREATE TABLE #t2 (testid INT, val VARCHAR(100))
INSERT INTO #t2 (testid, val)
VALUES (101, 'x')
     , (102, 'y')
     , (101, 'z')
     , (104, 'xx')

SELECT *
FROM (
    SELECT d1, d2, id, value = STUFF((
        SELECT [text()] = ',' + val
        FROM #t2
        WHERE #t2.testid = #t1.testid
        FOR XML PATH('')), 1, 1, '')      
    FROM #t1
) t
WHERE t.value IS NOT NULL

output - 

d1  d2  id   value
--- --- ---- ----------------
p   q   1    x,z
r   s   2    y
r   s   1    xx

Upvotes: 1

Related Questions