Need help converting rows to 3 columns

Transact-SQL
1

Hi, I have a table named AcctDxList...

AcctNo, DxCode
000123, A01
000123, A02
000123, B01
000123, B02
000123, C01
000124, A03
000124, B03
000124, C01
000124, C02

I want to convert this into this list...

AcctNo, Dx1, Dx2, Dx3
000123, A01, A02, B01
000124, A03, B03, C01

In other words, I want to get only the first three DxCode of each AcctNo and put them all in one row as the columns Dx1, Dx2, and Dx3.

How do I do this?

Thanks in advance for your much appreciated help!

2 
use sqlteam
go


declare @Columns varchar(2000)

create table #mtl777 (AcctNo varchar(10), DxCode varchar(10))

insert into #mtl777
select '000123', 'A01' union
select '000123', 'A02' union
select '000123', 'B01' union
select '000123', 'B02' union
select '000123', 'C01' union
select '000124', 'A03' union
select '000124', 'B03' union
select '000124', 'C01' union
select '000124', 'C02'

create table #funkzoid(
AcctNo varchar(50), 
DxCode varchar(50), 
DxGroup varchar(50)
)

insert into #funkzoid
select AcctNo, 
       DxCode, 
	   'Dx' + cast(headers as varchar(50))  as DxGroup
  from ( select AcctNo, DxCode,
		ROW_NUMBER() OVER (
				PARTITION BY AcctNo
				ORDER BY AcctNo
			) headers
		from #mtl777
	) src

SELECT @Columns =  COALESCE(@Columns + ', ','') + QUOTENAME(DxGroup)
FROM
(
	select distinct DxGroup from #funkzoid	 
) AS B
ORDER BY B.DxGroup

declare @query nvarchar(1500);

SET @query ='
select AcctNo, ' + @Columns + ' from
(
Select AcctNo, DxCode, DxGroup
	 From #funkzoid
) p
PIVOT 
(
MAX(DxCode)
FOR DxGroup in (' + @Columns + ')
) a '

exec( @query )

drop table #mtl777
drop table #funkzoid
1 Like
3 
SELECT
    AcctNo,
    MAX(CASE WHEN row_num = 1 THEN DxCode ELSE NULL END) AS Dx1,
    MAX(CASE WHEN row_num = 2 THEN DxCode ELSE NULL END) AS Dx2,
    MAX(CASE WHEN row_num = 3 THEN DxCode ELSE NULL END) AS Dx3
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY AcctNo ORDER BY DxCode) AS row_num
    FROM #mtl777
) AS query1
WHERE row_num BETWEEN 1 AND 3
GROUP BY AcctNo
1 Like
5

I like this shorter script.

Thanks everyone!