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
How to Debug Ora-01775: Looping Chain of Synonyms
Formula for Computed Column Based on Different Table's Column
Postgresql Generate Sequence with No Gap
Reference an Alias Elsewhere in the Select List
SQL Server Trigger Insert Values from New Row into Another Table
Restrict an SQL Server Connection to a Specific Ip Address
How to Flush Output from Pl/SQL in Oracle
How to Fill Date Gaps in MySQL
Why Using a Udf in a SQL Query Leads to Cartesian Product
How to Get a SQL Row_Number Equivalent for a Spark Rdd
SQL Server 2008 Paging Methods
Sqlite Select with Condition on Date
How to Rename Something in SQL Server That Has Square Brackets in the Name
How to Alter This Computed Column in SQL Server 2008
Access 2010: Syntax Error (Missing Operator) in Query Expression
SQL Insert into Database with Apostrophe
Why Does SQL Server Round Off Results of Dividing Two Integers