I'm trying to convert specific columns into rows group by the Number to follow after. These columns are linked to the PortBaseID as columns but I need to do two (2) things;
1. convert them to rows right after the main select (i.e. Row, Type, Name, Number, Start, End, Description, etc...)
2. hardcode the Row and Type with 'AT' and 'AC'
Below is the select query I'm using but I can't seem to find a way to properly get my expected results. I thought about doing a union or union all but I thought it might get complicated to maintain if I keep adding the columns I want to convert to rows.
select 'HI' as Row, 'AC' as Type
, p.ShortName as Name
, pb.PortfolioBaseCode as Number
, convert(varchar(19), pc.BillingStartDate,101) as Start
, convert(varchar(19), pc.BillingCloseDate,101) as End
, pb.ReportHeading1 as Description
, pc.PrincipalFeeAllocation as [Principal Fee Allocation]
, pc.CostCenter as [Cost Center]
, pc.AccountDomicile as [Account Domicile
from dbo.vPortBase pb
inner join dbo.vPort p on pb.PortBaseID = p.PortID
inner join AdvApp.vPortBaseCustom pc on pb.PortBaseID = pc.PortBaseID
inner join dbo.vPortGroupMember pgm on pb.PortBaseID = pgm.MemberID
inner join AdvApp.vPortGroup pg on pgm.PortGroupID = pg.PortGroupID
where pg.PortfolioGroupCode = 'group1'