Merge Row Values into a CSV (A.K.A Group_Concat for SQL Server)

Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server)

For each pair of EntityID, AttributeID use the XML path trick to generate the CSV

 SELECT
M.EntityID, M.AttributeID,
SUBSTRING(CAST(foo.bar AS varchar(8000)), 2, 7999) AS Options
FROM
(
SELECT DISTINCT EntityID, AttributeID
FROM MyTable
) M
CROSS APPLY
(
SELECT
',' + OptionText
FROM
MyTable M2
WHERE
M.EntityID = M2.EntityID AND M.AttributeID= M2.AttributeID
FOR XML PATH ('')
) foo(bar)

How to make Comma delimited data into individual rows in a new table in sql server

Try this one -

Query:

DECLARE @table TABLE 
(
a VARCHAR(200)
, b VARCHAR(200)
)

INSERT INTO @table(a, b)
VALUES
('1,2,3,', 'abc, def, ghi,'),
('1,2,3,4,', 'test1, test2, test3, test4,')

SELECT
[Item name] = LTRIM(data.p)
, [Item type] = data.r
FROM (
SELECT
p = p.value('(.)[1]', 'NVARCHAR(50)')
, po = p.value('for $i in . return count(../*[. << $i])', 'int')
, r = r.value('(.)[1]', 'NVARCHAR(50)')
, ro = r.value('for $i in . return count(../*[. << $i])', 'int')
FROM (
SELECT
txml = CAST('<r><s>' + REPLACE(SUBSTRING(t.a, 1, LEN(t.a) - 1), ',', '</s>' + '<s>') + '</s></r>' AS XML)
, kxml = CAST('<r><s>' + REPLACE(SUBSTRING(t.b, 1, LEN(t.b) - 1), ',', '</s>' + '<s>') + '</s></r>' AS XML)
FROM @table t
) d
CROSS APPLY kxml.nodes('/r/s') t(p)
CROSS APPLY txml.nodes('/r/s') k(r)
) data
WHERE data.po = data.ro

Output:

Item name   Item type
----------- ----------
abc 1
def 2
ghi 3
test1 1
test2 2
test3 3
test4 4

Merge record values into a record that contains multiple values with comma separated

They are several techniques to converting row values into a string, I like this one:

SELECT p1.product_id,
substring(
( SELECT ',' + engine_id
FROM ProductEngine p2
WHERE p2.product_id = p1.product_id
FOR XML PATH('') ), 2, 4000)
AS engine_id
FROM Products p1;

Edited

Now is working as you need. I have write an example to you.

SQL Server: combining multiple rows into one row

There are several methods.

If you want just the consolidated string value returned, this is a good quick and easy approach

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
AND ISSUE = 19602

SELECT @combinedString as StringValue

Which will return your combined string.

You can also try one of the XML methods e.g.

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ','
FROM jira.customfieldvalue v2
WHERE v2.Customfield = v1.Customfield
AND v2.Issue = v1.issue
ORDER BY ID
FOR XML PATH('') ) D ( StringValues )
WHERE customfield = 12534
AND ISSUE = 19602

How do I get a comma delimited result from result set without using parameters

With parameters (better performance):

DECLARE @list VARCHAR(max)

SELECT @list = COALESCE(@list + ', ' , '') + FirstName + ' ' + LastName
FROM table_of_Names where NameType='game show host'

SELECT @list

Without parameters (not as performant):

select stuff((select ','+ FirstName + ' ' + LastName
FROM table_of_Names where NameType='game show host'
for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as NameList

mysqli: merge rows in 1 based on some parameters

You can try to use condition aggregate function to make it.

Schema (MySQL v5.7)

CREATE TABLE T(
ID int,
Lang varchar(50),
Text varchar(50),
number int,
chapter int
);



INSERT INTO T VALUES (1, 'English', 'Hello', 2, 1);
INSERT INTO T VALUES (4, 'German', 'Hallo', 2, 1);
INSERT INTO T VALUES (5, 'Spanish', 'Hola', 2, 1);

Query #1

SELECT MAX(ID) id,
MAX(CASE WHEN Lang ='English' THEN Text END) lang1,
MAX(CASE WHEN Lang ='German' THEN Text END) lang2,
MAX(CASE WHEN Lang ='Spanish' THEN Text END) lang3,
number,
chapter
FROM T
GROUP BY number, chapter;

| id | lang1 | lang2 | lang3 | number | chapter |
| --- | ----- | ----- | ----- | ------ | ------- |
| 5 | Hello | Hallo | Hola | 2 | 1 |

View on DB Fiddle

SQL Server convert select a column and convert it to a string

You can do it like this:

Fiddle demo

declare @results varchar(500)

select @results = coalesce(@results + ',', '') + convert(varchar(12),col)
from t
order by col

select @results as results

| RESULTS |
-----------
| 1,3,5,9 |

Complex join with coalesce

Found this.

Very helpful. My code could probably use some cleaning up.



Related Topics



Leave a reply



Submit