Select Multiple Rows from Single Column into Single Row

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:























SOURCEDESTINATION
AB
CD
EF

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

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



Leave a reply



Submit