Select several rows into one row

Hi,

The pivot operator is perfect for this.

You didn't indicate exactly which way you wanted the results pivoted, so I've done both

Regards
  David

use EE
go

if object_id( N'tempdb..#transaction', N'U' ) is not null 
	drop table #transaction;
	
create table #transaction(
	CostNoSeq int
	, TipeTransaksi char( 3 )
	)

insert #transaction
	values( 1, 'BS' )
		, ( 2, 'GR' )	
		, ( 3, 'SR' )	
		, ( 4, 'SIA' )	
		, ( 5, 'STI' )	
		, ( 11, 'SJ' )	
		, ( 12, 'BRM' )	
		, ( 14, 'SOA' )	
		, ( 15, 'STO' )	

select *
from #transaction

select 
	[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15]
from #transaction t
pivot (
max( t.TipeTransaksi )
for t.CostNoSeq in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15] )
) as p
;

select 
	BS, GR, SR, SIA, STI, SJ, BRM, SOA, STO
from #transaction t
pivot (
max( t.CostNoSeq )
for t.TipeTransaksi in ( BS, GR, SR, SIA, STI, SJ, BRM, SOA, STO )
) as p
;

Select allOpen in new window

Hi David,

It works, but I don't want to limited by data, not hard coding such as.

[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15]
BS, GR, SR, SIA, STI, SJ, BRM, SOA, STO

Thank you.

Hi

Unfortunately, the column names have to be hardcoded - or you could use dynamic SQL.

It looks like your preference was the first pivot query rather than the second, no?

Adding to the above, here is some dynamic sql that will produce a result. Note that first of all it builds a numbers table, to create the numbers range.

Regards
  David

if object_id( N'tempdb..#Numbers', N'U' ) is not null 
	drop table #Numbers;
	
select top 10000 identity(int,1,1) AS i
into #Numbers
from sys.objects s1       --use sys.columns if you don't get enough rows returned to generate all the numbers you need
cross join sys.objects s2
;

declare @nos varchar( max )

select @nos = isnull( @nos, '' ) + concat( quotename( n.i ), ',' )
from #Numbers n
where
	n.i between
		( 
		select min( t.CostNoSeq )
		from #transaction t
		)
	and
		(
		select max( t.CostNoSeq )
		from #transaction t
		)
order by
	n.i asc
;

set @nos = left( @nos, len( @nos) - 1 )
;

declare @sql varchar( max )

set @sql = 'select ' + @nos + 'from #transaction t pivot ( max( t.TipeTransaksi ) for t.CostNoSeq in (' + @nos + ') ) as p'

execute( @sql )
;

Select allOpen in new window

The result.

1      2      3      4      5      6      7      8      9      10      11      12      13      14      15
BS       GR       SR       SIA      STI      NULL      NULL      NULL      NULL      NULL      SJ       BRM      NULL      SOA       STO

I don't want null value at the result.

Thank you.

Hi David,

Great. Just what I need.

Thank you very much for your help.

Hi emi_sastra

Generally, in a number sequence, the nulls are valuable and it pays to build out the sequence. After thinking about it, it was almost ridiculously easy to rebuild the sequence from the original table.

Regards
  David

If you just want a single string result, then  (using #transactions as the source table) :

select TipeTransaksi + ' ' from #transaction order by costnoseq for xml path('')

Select allOpen in new window

So you could declare a variable and set the value to those results ie

declare @transaction_list varchar(200)

set @transaction_list = (select TipeTransaksi + ' ' from #transaction order by costnoseq for xml path(''))

print @transaction_list

-- results :

BS  GR  SR  SIA STI SJ  BRM SOA STO 

Select allOpen in new window

And, if you dont like FOR XML, then you can build the string like this :

declare @transaction_list varchar(200)

select @transaction_list = isnull(@transaction_list + ' ','') + TipeTransaksi from #transaction order by costnoseq

print @transaction_list

Select allOpen in new window

Now in SQL2017 we have STRING_AGG() which is brilliant new feature : https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

I dont think you need a pivot... and I love pivot :)

Thank you Marks.

My pleasure :)