T-SQL Pivot Sort Order

Question

I have a table that has contact information ordered by sort order and then going down in the column.

Contacts Table:

I need to create a table where the emergency contact infomration is sorted by Columns instead of rows:

How can I do this?

Answer 1

Your dyanmic pivot query should be like in this demo

declare @cols nvarchar(max);


declare @query nvarchar(max);


select 
    sourceId,
    patientId,
    data,
    cols= concat(col,sortOrder)
into #t
from
    Contacts
    UNPIVOT
    (
    data for col in 
        (
            personalContactType_MisContactTypeId,
            personalContactNameLast,
            personalContactNameFirst
        )
    )up


select @cols= stuff((
                   select distinct  ','+ quotename(cols)
                   from #t 
                   for xml path('')),1,1,'')
select @cols

select @query= 'select * from #t
pivot
( max(data) for cols in ('+@cols+
'))p'

exec (@query)

Answer 2

In your dynamic pivot, use SortOrder to ORDER BY when creating your column list.