Sql Select Multiple Rows in One Column

SQL select multiple rows as one row

Since SQL Server 2017, SQL Server has supported string_agg():

select date,
       string_agg(id, ',') within group (order by id) as ids,
       string_agg(model, ',') within group (order by id) as model,
       string_agg(price, ',') within group (order by id) as prices
from transactions
group by date;

In older versions of SQL Server, you have to use the XML work-around.

SELECT multiple rows from single column into single row

You would use FOR XML PATH for this:

select p.name,
  Stuff((SELECT ', ' + s.skillName 
         FROM skilllink l
         left join skill s
           on l.skillid = s.id 
         where p.id = l.personid
         FOR XML PATH('')),1,1,'') Skills
from person p

See SQL Fiddle with Demo

Result:

| NAME |            SKILLS |
----------------------------
| Bill | Telepathy, Karate |
|  Bob |            (null) |
|  Jim |         Carpentry |

How to merge multiple rows in one single column field with given condition in SQL SERVER 2008

How about something like

DECLARE @Table TABLE(
        acc_no VARCHAR(50),
        name VARCHAR(50)
)
INSERT INTO @Table (acc_no,name) SELECT '001-000001', 'John'
INSERT INTO @Table (acc_no,name) SELECT '001-000001', 'Bob'
INSERT INTO @Table (acc_no,name) SELECT '001-000001', 'James'
INSERT INTO @Table (acc_no,name) SELECT '001-000002', 'Sam'
INSERT INTO @Table (acc_no,name) SELECT '001-000002', 'Bin'
INSERT INTO @Table (acc_no,name) SELECT '001-000002', 'Dus'

--Concat
SELECT  t.acc_no,
        stuff(
                (
                    select  ',' + t1.name
                    from    @Table t1
                    where   t1.acc_no = t.acc_no
                    order by t1.name
                    for xml path('')
                ),1,1,'') Concats
FROM    @Table t
GROUP BY t.acc_no

SQL Fiddle DEMO

Select Multiple rows in single column separated by New Line

This should do

SELECT name, GROUP_CONCAT(fruit SEPARATOR '\n') FROM your_table GROUP BY name

Demo in db<>fiddle

Update to add numbering:

SELECT name ,
       GROUP_CONCAT(CONCAT (rn,')',fruit) SEPARATOR '\n')
FROM (
    SELECT * 
        ,ROW_NUMBER() OVER (PARTITION BY name) AS rn
    FROM your_table
    ) SQ
GROUP BY name

Demo with numbering in db<>fiddle

Merge multiple rows into one column without duplicates

For SQL Server you can use:

select player,
  stuff((SELECT distinct ', ' + cast(score as varchar(10))
           FROM yourtable t2
           where t2.player = t1.player
           FOR XML PATH('')),1,1,'') 
from yourtable t1
group by player

Select multiple rows into a single row

Use STRING_AGG(col1)

   select Sequence-ID , STRING_AGG(Value , ';')  AS newValue
    from table
    GROUP  BY Sequence-ID;

MySQL - select multiple rows from one table into one result row

SOLUTION 1

You have data something like this:

SQL for create table:

CREATE TABLE `my_cpu` (
  `name` varchar(32) NOT NULL,
  `cpu_count` tinyint(4) DEFAULT NULL,
  `memory_count` tinyint(4) DEFAULT NULL,
  `import_date` date NOT NULL,
  PRIMARY KEY (`name`,`import_date`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii

RESULT:

SQL for create that result:

SELECT a.name,
    a.cpu_count AS cpu_old,
    a.memory_count AS memory_old,
    b.cpu_count AS cpu_new  ,
    b.memory_count AS memory_new
FROM `my_cpu`   a INNER JOIN `my_cpu` b ON a.name = b.name
WHERE a.import_date = "2022-09-28" AND b.import_date = "2022-10-01"

EXPLAINATION:

• Because you store data every week, so you have 2 dates.
• The data is saved in one table_names, lets say it "my_cpu".
• Create self join from my_cpu. So you have 2 table (a and b). With condition a.name = b.name
• Filter where a.import_date = your_old_date and b.import_date =
  your_new_date

FURTHER READ:

https://www.w3schools.com/sql/sql_join_self.asp

---

ALTERNATE SOLUTION:

This would be suitable for random/ different import date

Using group_concat and substring_index for different import date.

RESULT:

SQL:

SELECT  `name`,

    SUBSTRING_INDEX(SUBSTRING_INDEX(cpu_count,",",2),",",-1) cpu_old,
    SUBSTRING_INDEX(SUBSTRING_INDEX(memory_count,",",2),",",-1) memory_old,
    
    SUBSTRING_INDEX(cpu_count,",",1) cpu_new,
    SUBSTRING_INDEX(memory_count,",",1) memory_new
FROM
(

SELECT  `name`,
    GROUP_CONCAT(`cpu_count` ORDER BY `import_date` DESC) cpu_count,
    GROUP_CONCAT(`memory_count` ORDER BY `import_date` DESC) memory_count
FROM    `my_cpu`
GROUP
BY NAME
)

AS dbx

EXPLAINATION:

Create query for get cpu and memory in ordered by import_date in descending each cpu_name

THE QUERY:

SELECT  `name`,
    GROUP_CONCAT(`cpu_count` ORDER BY `import_date` DESC) cpu_count,
    GROUP_CONCAT(`memory_count` ORDER BY `import_date` DESC) memory_count
FROM    `my_cpu`
GROUP
BY NAME

WOULD REPRODUCE:

Search with substring_index,

The lastet would be the first letter before (,)

The second(oldest) would be the the second letter after the (,)

---

Related Topics