T SQL combine rows

Transact-SQL
1

I have a requirement where there are two rows for a given ID (VisitID) ( sample table below) and as can be seen the first two rows all of the data from columns VisitID to DateTime is same but columns after that are different.
I would like to have it in a single row for a givenID. Any help is greatly appreciated.
Thanks,
Current Table

VisitID Request Process Complete DateTime FromLoc ToLoc XPORTMode MatorSlide SA1 SA2 DelayCancel
FN10002454481 5/7/19 17:45 5/7/19 17:58 5/7/19 18:40 5/7/19 17:45 PACU 5W BED NULL CLX NULL NULL
FN10002454481 5/7/19 17:45 5/7/19 17:58 5/7/19 18:40 5/7/19 17:45 PACU 5W BED NULL NULL NLC NULL
FN10002454481 5/9/19 10:47 5/9/19 11:02 5/9/19 11:17 5/9/19 10:47 5W MLOB WHEELCHAIR NULL YCT NULL NULL

Required output

VisitID Request Process Complete DateTime FromLoc ToLoc XPORTMode MatorSlide SA1 SA2 DelayCancel
FN10002454481 5/7/19 17:45 5/7/19 17:58 5/7/19 18:40 5/7/19 17:45 PACU 5W BED NULL CLX NLC NULL
FN10002454481 5/9/19 10:47 5/9/19 11:02 5/9/19 11:17 5/9/19 10:47 5W MLOB WHEELCHAIR NULL YCT NULL NULL
2

please post your sample data not as a embedded table but as follows

create table #sample(VisitID varchar(150), Request datetime, Process datetime,
Complete datetime, _DateTime datetime, FromLoc varchar(10), XPORTMode varchar(10))

insert into #sample
select 'FN10002454481', '2019-05-07' ------etc fill in the rest
3

This can be resolved using a GROUP BY statement and grouping on all of the columns that 'do not change'. The other columns will then be returned using MAX...

4

If all of the columns are the same for the 1st 2 rows, you can select distinct in front of it. Just a question though: Why is the data like this? is this a view that isn't correct? I would look into fixing the source issue instead of putting something in to fix it. these type of 'fixes' can lead to performance problems

1 Like
5

DISTINCT will not work because there are different values on separate rows for the other columns.

6

Jeff, I'll disagree with you. Based on the just the information above, distinct would return then 2 rows that the op was requesting. I don't know the data, so I'm only guessing. I would defer to my original response and fix the source.

7

There are 3 rows in the original set - reduced to 2 rows as the expected final result. The first 2 rows of the original data has the value CLX in the column SA1 on row 1 and the value NLC in the column SA2 in row 2...

The expected result has the value CLX in SA1 and NLC in SA2 for XPORTMode = BED.

No way could DISTINCT get that result...because NULL and CLX are not equal values.

8

Hi,

Here you go. You can make the column as Max if not the same value. This is just a concept of query based on your input & output. Null have to replace with empty string else MAX() function cannot be applied.

select visitid,Request,Process,Complete,DateTme,FromLoc=Max(Isnull(FromLoc,'')),ToLoc=Max(Isnull(ToLoc,'')),MatorSlide=Max(Isnull(MatorSlide,'')),SA1=Max(Isnull(SA1,'')),SA2=Max(Isnull(SA2,'')),DelayCancel=Max(Isnull(DelayCancel,'')) 
from (
	select visitid='FN10002454481',Request='5/7/19 17:45',Process='5/7/19 17:58',Complete='5/7/19 18:40',DateTme='5/7/19 17:45',FromLoc='PACU',ToLoc='5W',XPORTMode='BED', MatorSlide=NULL,SA1='CLX',SA2=NULL,DelayCancel=NULL  union all
	select 'FN10002454481','5/7/19 17:45','5/7/19 17:58','5/7/19 18:40','5/7/19 17:45','PACU','5W','BED',NULL,NULL,'NLC',NULL union all
	select 'FN10002454481','5/9/19 10:47','5/9/19 11:02','5/9/19 11:17','5/9/19 10:47','5W','MLOB','WHEELCHAIR',NULL,'YCT',NULL,NULL 
)X
group by visitid,Request,Process,Complete,DateTme

Regards,
Micheale