declare @transactionid int = 4
declare @data table ( subtype varchar(max), value varchar(max) )

insert into @data
values (‘Header Data’, ”), (‘———-‘, ‘———-‘)
insert into @data
SELECT Field, Value
FROM
(
SELECT convert(varchar(max), o.DisplayName) as Organization,
convert(varchar(max), w.computername) as Workstation,
convert(varchar(max), pu.Name) as PolarisUser,
convert(varchar(max), th.TransactionDate, 20) as TransactionDate,
convert(varchar(max), tt.TransactionTypeDescription) as TransactionType
FROM PolarisTransactions.polaris.TransactionHeaders th
join polaris.polaris.organizations o on th.OrganizationID = o.OrganizationID
join polaris.polaris.Workstations w on th.WorkstationID = w.WorkstationID
join polaris.polaris.PolarisUsers pu on th.PolarisUserID = pu.PolarisUserID
join PolarisTransactions.polaris.TransactionTypes tt on th.TransactionTypeID = tt.TransactionTypeID
where TransactionID = @transactionid
) Main
UNPIVOT
(
Value FOR Field IN ( Organization, Workstation, PolarisUser, TransactionDate, TransactionType )
) Sub

insert into @data values ( ”,” ), ( ‘Transaction Details’, ” ), (‘———-‘, ‘———-‘)
insert into @data
select tst.TransactionSubTypeDescription,
case
when tst.DataType = ‘N’ then convert(varchar(max), td.numvalue)
when tst.DataType = ‘D’ then convert(varchar(10), td.dateValue, 20)
when tst.DataType = ‘T’ then ( select tds.TransactionString from PolarisTransactions.polaris.TransactionDetailStrings tds where tds.TransactionStringID = td.numValue )
when tst.DataType = ‘K’ then
(
select coalesce((select tstc.TransactionSubTypeCodeDesc from PolarisTransactions.polaris.TransactionSubTypeCodes tstc where tstc.TransactionSubTypeID = td.transactionsubtypeid and tstc.TransactionSubTypeCode = td.numvalue), convert(varchar(max), td.numvalue) )
)
else convert(varchar(max), td.numValue)
end
from PolarisTransactions.polaris.TransactionDetails td
join PolarisTransactions.polaris.TransactionSubTypes tst on
td.TransactionSubTypeID = tst.TransactionSubTypeID
where td.TransactionID = @transactionid
select * from @data