Query to output results in one row for all combinations-CodePudding

I want to convert the output to one row from multiple columns. How should I update the attached code.
This is my current output:

OrigSamp    DupSamp1
23407   23414
23420   23424
23420   23430
23420   23431

This is the expected output I want i.e. for every value in the first column OrigSamp, the relevant values in cols 2,3,4 should be in the same row. If no value then return NULL.

OrigSamp    DupSamp1    DupSamp2    DupSamp3
23407       23414       NULL        NULL
23420       23424       23430       23431

My code below: Any help is appreciated.

declare @tbl table(OrigSamp int, DupSamp1 int, OrigRes varChar(10), DupRes1 varChar(10))

INSERT INTO @tbl
(OrigSamp,DupSamp1,OrigRes, DupRes1 )
SELECT 
s.SAMPLE_NUMBER as [OrigSamp]
,s1.SAMPLE_NUMBER  as [DupSamp1]
,r.FORMATTED_ENTRY as [OrigRes]
,r1.FORMATTED_ENTRY  as [DupRes1]
FROM 
SAMPLE s
INNER JOIN SAMPLE s1 on s.SAMPLE_NUMBER = s1.PARENT_SAMPLE
INNER JOIN RESULT r on s.SAMPLE_NUMBER = r.SAMPLE_NUMBER
INNER JOIN RESULT r1 on r1.SAMPLE_NUMBER = s1.SAMPLE_NUMBER
WHERE
s.LOCATION = 'TAURANGA' 
and s.STATUS = 'C' and s1.STATUS = 'C'
and s1.SAMPLE_NAME = 'DUP' 
and s.PARENT_SAMPLE = 0
and r.ANALYSIS in ('BC-B19_ISSUE_6-CENTR','BCPSD-ASTM_C136-CENTR')
and r1.ANALYSIS in ('BC-B19_ISSUE_6-CENTR','BCPSD-ASTM_C136-CENTR')
and r.NAME = 'Bitumen content'
and r1.NAME = 'Bitumen content'

declare @pivotcols varchar(200) = (STUFF((SELECT distinct ','   QUOTENAME(c.rn) 
FROM (select *
, 'DupSamp'   cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
from
@tbl) c
            
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,''))

declare @query varchar(max);

select * 
into #temp1 
from @tbl

set @query =  
'select OrigSamp,'   @pivotcols   ' from
(select OrigSamp,DupSamp1 as [samp1],''DupSamp'' 
  cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
from
#temp1
)t
pivot
(
max(samp1) for rn in ('    @pivotcols   ')
)p'

exec(@query)

drop table #temp1

Current output with the above code:
OrigSamp DupSamp1 DupSamp2  DupSamp3
23420     23424     23430   23431                                                                     

required output      
OrigSamp DupSamp1 DupSamp2 DupSamp3 OrigRes DupRes1 DupRes2 DupRes3
23420    23424   23430     23431    6.2     6.9     6.1     6.6

CodePudding user response：

You can bring the desired output using dynamic pivot operation.

Try the below sample out.

declare @tbl table(OrigSamp int, DupSamp1 int)

insert into @tbl
values(23407,23414)
,(23420,23424)
,(23420,23430)
,(23420,23431)

declare @pivotcols varchar(200) = (STUFF((SELECT distinct ','   QUOTENAME(c.rn) 
            FROM (select *
            , 'DupSamp'   cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
            from
            @tbl) c
            
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,''))

declare @query varchar(max);

select * 
into #temp1 
from @tbl

set @query =  
'select OrigSamp,'   @pivotcols   ' from
(select OrigSamp,DupSamp1 as [samp1],''DupSamp'' 
  cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
from
#temp1
)t
pivot
(
max(samp1) for rn in ('    @pivotcols   ')
)p'

exec(@query)

drop table #temp1