Combining multiple row results to sing

Other SQL Server Topics
1

Hi! Still new to SQL and learning... I am using Microsoft SQL Server and am curious to know, when a result has multiple rows for the same unique ID can those rows be moved to individual columns instead?


SELECT 
       P.Pract_ID
     , PS.Specialty_name

FROM Practitioner P

JOIN Practitioner_Specialties PS on P.PRACT_ID = PS.PRACT_ID

I get this:

PRACT_ID Specialty
110 Nurse Practitioner [Nurse Practitioner]
110 Acute Care [Nurse Practitioner]
252 Foot & Ankle Surgery [Podiatrist]
252 Foot Surgery [Podiatrist]
365 Internal Medicine [Internal Medicine]
365 Pulmonary Disease [Internal Medicine]

But would like to see this:

PRACT_ID Specialty1 Specialty2
110 Nurse Practitioner [Nurse Practitioner] Acute Care [Nurse Practitioner]
252 Foot & Ankle Surgery [Podiatrist] Foot Surgery [Podiatrist]
365 Internal Medicine [Internal Medicine] Pulmonary Disease [Internal Medicine]
2

yes you can. First you write a query with a rownumber so you can use the rownumber to pivot the rownumbers 1 and the rownumbers 2.

/* Create testcase */
DROP TABLE IF EXISTS #Specialty;

CREATE TABLE #Specialty
(
	PRACT_ID Smallint,
	Specialty VARCHAR(250)
);

INSERT INTO #Specialty (PRACT_ID, Specialty)
SELECT 110,	'Nurse Practitioner [Nurse Practitioner]'
UNION
SELECT 110,	'Acute Care [Nurse Practitioner]';

/* See what we got */
SELECT * FROM #Specialty;

/* Create a rownumber */
SELECT *, ROW_NUMBER() OVER (PARTITION BY PRACT_ID ORDER BY Specialty) AS RowNumber
FROM #Specialty;

/* PIVOT the RowNumber */
WITH SpecialtyRowNumber AS
(
SELECT
	PRACT_ID,
	Specialty,
	ROW_NUMBER() OVER (PARTITION BY PRACT_ID ORDER BY Specialty) AS RowNumber
	FROM #Specialty
)
SELECT
	PRACT_ID,
	[1] AS First_Specialty,
	[2] AS Second_Specialty
FROM 
(
SELECT PRACT_ID,
	Specialty, RowNumber 
	FROM SpecialtyRowNumber
) p
PIVOT 
(MAX(Specialty) FOR RowNumber IN ([1],[2])) tbl;

Using PIVOT and UNPIVOT - SQL Server | Microsoft Learn

3

hi another way of doing this

while creating the table
i put identity column and inserted the values

this solution assumes that there will always be 2 row only for each Pract_ID

create table script

DROP TABLE IF EXISTS #Specialty;

CREATE TABLE #Specialty
(
Row_Num Int identity(1,1),
PRACT_ID Smallint,
Specialty VARCHAR(250)
)

INSERT INTO #Specialty (PRACT_ID, Specialty)
select 110, 'Nurse Practitioner [Nurse Practitioner]'
union
select 110, 'Acute Care [Nurse Practitioner]'
union
select 252, 'Foot & Ankle Surgery [Podiatrist]'
union
select 252, 'Foot Surgery [Podiatrist]'
union
select 365, 'Internal Medicine [Internal Medicine]'
union
select 365, 'Pulmonary Disease [Internal Medicine]'

select 
   pract_id 
   , max(case when Row_Num%2 = 1 then Specialty end) as Speciality1
   , max(case when Row_Num%2 = 0 then Specialty end) as Speciality2
from 
   #Specialty 
group by 
   pract_id

image