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
SQL Server: Combine multiple rows into one row from a join table?
Use a GROUP BY
query with STRING_AGG
:
SELECT
t1.ID,
t1.Data1,
STRING_AGG(t2.DataFromTable2, ',') AS data
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.ID = t2.ID
GROUP BY
t1.ID,
t1.Data1
WHERE
t1.ID = 1;
I am assuming you are using SQL Server 2017 or later. For earlier versions of SQL Server:
SELECT
t1.ID,
t1.Data1,
data = STUFF((
SELECT ',' + t2.DataFromTable2
FROM Table2 t2
WHERE t2.ID = t1.ID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table1 t1;
SQL Server : merge multiple rows into 1 row
I would try with aggregation:
select Id,
max(case when seq = 1 then code end) as code1,
max(case when seq = 1 then name end) as name1,
max(case when seq = 2 then code end) as code2,
max(case when seq = 2 then name end) as name2,
max(case when seq = 3 then code end) as code3,
max(case when seq = 3 then name end) as name3
from (select d.*,
row_number() over (partition by code order by order) as seq
from data d
) d
group by Id;
EDIT : If you have a already sequence
(i.e. Order
) then only aggregation is enough :
select Id,
max(case when [order] = 1 then code end) as code1,
max(case when [order] = 1 then name end) as name1,
max(case when [order] = 2 then code end) as code2,
max(case when [order] = 2 then name end) as name2,
max(case when [order] = 3 then code end) as code3,
max(case when [order] = 3 then name end) as name3
from data d
group by Id;
EDIT : Thanks to JohnLBevan for Demo.
Here is SQL Fiddle Example.
How to merge multiple rows into one row with filtering rules in SQL Server
You can try with conditional aggregation -
select MedicalCardId,
max(case when DiagnosisType='Main' then Symptom end) as MainSymptom,
max(case when DiagnosisType='Secondary' and DiagnosisOrder=1 then Symptom end) as SecondarySymptom1,
max(case when DiagnosisType='Secondary' and DiagnosisOrder=2 then Symptom end) as SecondarySymptom2,
max(case when DiagnosisType='Secondary' and DiagnosisOrder=3 then Symptom end) as SecondarySymptom3
from tablename
group by MedicalCardId
Combine Multiple Rows And Columns Into A Single Row In SQL
Try the following:
SELECT
OrderID,
Max(XboxLive)
Max(iTunes),
Max(XboxDate),
Max(iTunesDate)
Serial
FROM SampleTable
Group by
OrderID,
Serial
Merging multiple rows with same column in SQL Server
Use This
SELECT category,segment,payment = STUFF((
SELECT '+' + CONVERT(VARCHAR,payment)
FROM TABLENAME t
WHERE t.category = TABLENAME.category AND t.segment = TABLENAME.segment
FOR XML PATH('')
), 1, 1, '')
FROM TABLENAME
GROUP BY category,segment
if you get payment with + sign then use stuff like this.
SELECT category,segment,SUM(payment) as payment
FROM TABLENAME
GROUP BY category,segment
if you want sum then remove stuff and use Sum(payment)
only.
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
Combine multiple rows into single row in SQL View (without group by or with CTE?)
Your "magic function" would appear to be STRING_AGG()
.
The code would then look like this:
CREATE VIEW MyView AS
SELECT Staff.Id,
STRING_AGG(CONCAT(OtherStaff.Name, ', ', OtherStaff.Value), '; ') WITHIN GROUP (ORDER BY OtherStaff.Name),
SomeTable.SomeVal
FROM dbo.[Staff] Staff JOIN
dbo.[OtherStaff] OtherStaff
ON OtherStaff.StaffId = Staff.Id JOIN
dbo.[SomeTable] SomeTable
ON SomeTable.StaffId = Staff.Id
GROUP BY Staff.Id, SomeTable.SomeVal;
Listing unaggregated columns in both the GROUP BY
and SELECT
should not be too troublesome. After all, you can just cut-and-paste them.
Related Topics
T-Sql Query to Get the String Between Two Special Characters
H2 Database. How to Convert Date to Seconds in SQL
Combine Multiple Columns from Database into One Column
Sql Server Query to Find All Permissions/Access for All Users in a Database
Sql Server Pass Column Name as Where Clause Parameter
Sql Take Just the Numeric Values from a Varchar
How to Get Max Date Value of Date Column in Multiple Tables
Sql Server: Create an Incremental Counter for Records in the Same Year
Calling an API from SQL Server Stored Procedure
Mysql Table Insert If Not Exist Otherwise Update
Selecting Same Column Twice from a Single Table But With Different Conditions
Constraint for Phone Number in SQL Server
How to Select the Last Record from MySQL Table Using SQL Syntax
The MySQL Service on Local Computer Started and Then Stopped