CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
cmpmd2
cmpmd2

Reputation: 165

T-SQL Pivot Sort Order

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

Contacts Table: enter image description here

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

enter image description here

How can I do this?

Upvotes: 0

Views: 99

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17146

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)

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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

Upvotes: 0

Related Questions