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?
 
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
 
KANEDA 0149Author Commented:
Thank you, this is what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.