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 |
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
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;
Convert multiple rows into single row with more columns
Several options here.
You may notice that I listed the 3 column in the subquery. You need to "feed" your PIVOT with only the required columns.
Known Columns to Pivot
Select *
From (
Select ID
,Prop
,Value
From YourTable
) src
Pivot (max(Value) for Prop in ([P1],[P2],[P3]) ) pvt
Dynamic Pivot and version <2017
Declare @SQL varchar(max) = '
Select *
From (
Select ID
,Prop
,Value
From YourTable
) A
Pivot (max([Value]) For [Prop] in (' + stuff((Select Distinct ','+QuoteName(Prop)
From YourTable
Order By 1
For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
Dynamic Pivot for 2017+
Declare @SQL varchar(max) = '
Select *
From (
Select ID
,Prop
,Value
From YourTable
) A
Pivot (max([Value]) For [Prop] in (' + (Select string_agg(quotename(Prop),',')
From (Select distinct Prop From YourTable ) A) + ') ) p'
Exec(@SQL);
How to select single row when there are multiple rows with same meaning?
Use DISTINCT
and the LEAST
and GREATEST
functions:
SELECT DISTINCT
LEAST(source, destination) AS source,
GREATEST(source, destination) AS destination
FROM table_name
Which, for the sample data:
CREATE TABLE table_name (source, destination, distance) AS
SELECT 'A', 'B', 100 FROM DUAL UNION ALL
SELECT 'A', 'B', 110 FROM DUAL UNION ALL
SELECT 'B', 'A', 120 FROM DUAL UNION ALL
SELECT 'C', 'D', 200 FROM DUAL UNION ALL
SELECT 'D', 'C', 290 FROM DUAL UNION ALL
SELECT 'E', 'F', 310 FROM DUAL UNION ALL
SELECT 'F', 'E', 320 FROM DUAL UNION ALL
SELECT 'F', 'E', 300 FROM DUAL;
Outputs:
SOURCE DESTINATION A B C D E F 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_noSQL Fiddle DEMO
SQL SERVER Combine multiple rows with multiple columns into single row with single column
Here is an example with your data:
select t1.*,
stuff( (select '; ' + coalesce(data1, '') + ' ' + coalesce(data2, '')
from table2 t2
where t2.FK_TBL1_ID = t1.id
for xml path ('')
), 1, 2, ''
) as Data1Data2
from table1 t1;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
Related Topics
Sql: Insert Into...Values..Select
Looping Through Recordset with Vba
How to Set Named Parameter on Bigquery Query
What's The Default Window Frame for Window Functions
Convert from Uniqueidentifier to Bigint and Back
What Is The Correct Case Select Statement in Access 2010
Delete Duplicate Records Using Rownum in Sql
Data Type Equivalents: Ms Access Tables ↔ 'Create Table' Queries ↔ Odbc Sql
Fillfactor for a Sequential Index That Is Pk
Error- Ora-22835: Buffer Too Small for Clob to Char or Blob to Raw Conversion
Why Can't I Reorder My SQL Server Columns
What Is The Limit of The Field Type Bigint in Sql
Is There a Tool to Generate a Full Database Ddl for SQL Server? What About Postgres and MySQL
Why Am I Getting a an Error When Creating a Generated Column in Postgresql
Schema, Owner for Objects in Ms Sql