Combining multiple rows

Question

I have this thing I don't know how to solve.

TABLE 1: ITEMS

ID | NO_ 
1 | SX1234
2 | SXABCD

TABLE 2: Text

Reffilename | RefKey | LanguageCode | ID | LineNumber | Txt1
Product | SX1234 | ENU | 2 | 0 | This is
Product | SX1234 | ENU | 2 | 1 | my text
Product | SX1234 | ENU | 2 | 2 | I want to come 
Product | SX1234 | ENU | 2 | 3 | out in one field
....

I can do this

SELECT i.[No]_, t.[Txt1] 
FROM ITEMS AS i
INNER JOIN 
TEXT AS t ON i.[no_] = t.RefKey
WHERE t.Reffilename = 'Prouduct' AND t.LanguageCode = 'ENU' AND t.ID = 2 AND i.[No_] = 'SX1234'

Thats give me this result.

SXI1234 | This is
SXI1234 | my text
SXI1234 | I want to come
SXI1234 | out in one field

But I want to merge the text, so my output will come:

SXI1234 | This is my text I Want to come out in one field

How do i solve this?

Take a look at https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — jarlh, Nov 14 '18 at 09:48

score 0 · Answer 1 · answered Nov 14 '18 at 10:05

0

SELECT a.RefKey,Stuff((SELECT ''+ Txt1  FROM Text
where a.Id=b.Id
 FOR
 XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N''
) As Text 
from ITEMS a
group by a.Id

answered Nov 14 '18 at 10:05

Srinivasan Rajasekaran

555
3
17

score 0 · Answer 2 · answered Nov 14 '18 at 10:25

Try using STUFF() function

SELECT i.[No]_, text= STUFF((
    SELECT ', ' + t.[Txt1] 
    FROM ITEMS AS a
    INNER JOIN 
    TEXT AS t ON i.[no_] = t.RefKey
    WHERE a.[No]_= i.[No]_
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM ITEMS AS i
GROUP BY i.[No]_

score 0 · Answer 3 · edited Nov 14 '18 at 10:34

0

DECLARE @t1 TABLE (Id INT, Num CHAR (7))
INSERT INTO @t1
    VALUES (1, 'SX1234'), (2, 'SXABCD')
DECLARE @t2 TABLE (Reffilename Char(8), RefKey CHAR (7), LanguageCode  CHAR(4), ID INT, LineNumber INT, Txt1 VARCHAR(MAX))
INSERT INTO @t2
    VALUES ('Product', 'SX1234', 'ENU', 2, 0, 'This isa,
    ('Product', 'SX1234', 'ENU', 2, 1, 'my text'),
    ('Product', 'SX1234', 'ENU', 2, 2, 'I want to come'),
    ('Product', 'SX1234', 'ENU', 2, 3, 'out in one field')

SELECT DISTINCT RefKey, Result = STUFF((SELECT ' ' + t2.Txt1
                                FROM @t1 t1
                                JOIN @t2 t2 ON t1.Num = t2.RefKey 
                                FOR XML PATH('')), 1, 1, '' ) 
FROM @t2

edited Nov 14 '18 at 10:34

Thomas G

9,886
7
28
41

answered Nov 14 '18 at 10:30

Rigersong

1
1

It would be beneficial if you explain your solution with more than just code. As the idea of SO is not to just copy some code that does 'the job' but also to learn :) – thefolenangel Nov 14 '18 at 10:40
At first, declare all tables and variables (it’s clear). Then use function stuff(). First argument – query (add free space before each word, select query like XML), second – start from first symbol, third – character length to remove, last - replaces the character length of the expression I hope that I translated my thoughts correctly) – Rigersong Nov 14 '18 at 14:05

Combining multiple rows

3 Answers3