Transform rows to comma delimited string by groups of n records
So you can use a modified row number (divided by 2) to group your data into blocks of 2 rows, and then you can use string_agg()
.
You do need a way to order the rows though (if you want consistent results), I have assumed NTID
will work, but you may have a better column to order by.
declare @Test table (ID int, [NAME] varchar(32), ZIP varchar(12), NTID varchar(2));
insert into @Test (ID, [NAME], ZIP, NTID)
values
(1, 'Juan', '123', 'H1'),
(1, 'Juan', '123', 'H2'),
(2, 'John', '456', 'H3'),
(2, 'John', '456', 'H4'),
(2, 'John', '456', 'H5');
with cte as (
select *
, (row_number() over (partition by ID order by NTID) - 1) / 2 rn
from @Test
)
select ID, [NAME], string_agg(NTID,',')
from cte
group by ID, [NAME], rn;
db<>fiddle here
Note: If you supply the DDL+DML as part of your question you make it much easier to answer.
How can I get comma delimited values into new row in BigQuery?
Try this one:
with mytable as (
select "X" as model, "1,34,60,1000" as alarm union all
select "Y", "2,4,5,900,1000" union all
select "Z", "1"
)
select model, newalarm
from mytable, unnest(split(alarm)) as newalarm
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
How to turn a pandas dataframe row into a comma separated string
You could use pandas.DataFrame.to_string
with some optional arguments set to False and then split on newline characters to get a list of your strings. This feels a little dirty though.
x = df3.to_string(header=False,
index=False,
index_names=False).split('\n')
vals = [','.join(ele.split()) for ele in x]
print(vals)
Outputs:
['1.221365,0.923175,-1.286149,-0.153414,-0.005078', '-0.231824,-1.131186,0.853728,0.160349,1.000170', '-0.147145,0.310587,-0.388535,0.957730,-0.185315', '-1.658463,-1.114204,0.760424,-1.504126,0.206909', '-0.734571,0.908569,-0.698583,-0.692417,-0.768087', '0.000029,0.204140,-0.483123,-1.064851,-0.835931', '-0.108869,0.426260,0.107286,-1.184402,0.434607', '-0.692160,-0.376433,0.567188,-0.171867,-0.822502', '-0.564726,-1.084698,-1.065283,-2.335092,-0.083357', '-1.429049,0.790535,-0.547701,-0.684346,2.048081']
Convert multiple rows into a single comma separated string without XML
#standardSQL
select id, string_agg(food) as foods
from `project.dataset.table`
group by id
Convert multiple rows into one with comma as separator
This should work for you. Tested all the way back to SQL 2000.
create table #user (username varchar(25))
insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')
declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user
select SUBSTRING(@tmp, 0, LEN(@tmp))
Related Topics
MySQL - How to Unpivot Columns to Rows
MySQL Insert Query Doesn't Work With Where Clause
Is the SQL Where Clause Short-Circuit Evaluated
How to Update Only One Field Using Entity Framework
How to Use Case Statement in a Join Condition
SQL Server 2008 Management Studio Not Checking the Syntax of My Query
How to Update Two Tables in One Statement in SQL Server 2005
Computed/Calculated/Virtual/Derived Columns in Postgresql
Return Row With the Max Value of One Column Per Group
Insert Statement Conflicted With the Foreign Key Constraint - SQL Server
Entity Attribute Value Database Vs. Strict Relational Model Ecommerce
Equivalent of Limit and Offset For SQL Server
How to Change MySQL Table Names in Linux Server to Be Case Insensitive
How to Concatenate Multiple MySQL Rows into One Field
How to Get Matching Data from Another SQL Table For Two Different Columns: Inner Join And/Or Union