TSQL Dynamic Convert Columns To Rows

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'

Open in new window


Example.png
KANEDA 0149Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
You could do it like this, start by querying the 'pc' table 3 times to pull the data into a normalized format then union the "header" values with the others.
I added the portbaseid to the output so you have a means to keep the data ordered correctly. It's not dynamic but is fairly sustainable to add additional pieces. Obviously I don't have your data so there may be some syntax issues.

DECLARE @Data Table (
      Description varchar(50),
      Value varchar(50),
      PortBaseID int
      )
INSERT INTO @Data (Description, Value, PortBaseID)
SELECT 'Principal Fee Allocation',
      PrincipalFeeAllocation,
      PortBaseID
FROM  AdvApp.vPortBaseCustom

INSERT INTO @Data (Description, Value, PortBaseID)
SELECT 'Cost Center',
      CostCenter,
      PortBaseID
FROM  AdvApp.vPortBaseCustom

INSERT INTO @Data (Description, Value, PortBaseID)
SELECT 'Account Domicile',
      AccountDomicile,
      PortBaseID
FROM  AdvApp.vPortBaseCustom

select pb.PortBaseID,
      '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
 
from dbo.vPortBase pb
inner join dbo.vPort p on pb.PortBaseID = p.PortID
inner join dbo.vPortGroupMember pgm on pb.PortBaseID = pgm.MemberID
inner join AdvApp.vPortGroup pg on pgm.PortGroupID = pg.PortGroupID
where pg.PortfolioGroupCode = 'group1'

UNION

SELECT PortBaseID,
      'AT',--row
      'AC',--type
      Description, --name
      Value, --number
      Null, --start
      Null, --end
      Null --Description
FROM @Data
ORDER BY PortBaseID, [Row] DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KANEDA 0149Author Commented:
Thank you, this is what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.