Pivot table columns based on select statement rather than being hardcoded.

This query gives the following output.

;With ID as
(
SELECT cu.InstrumentID,
RelationshipType,
de.DsValue,
ma.DSOrgCode
from gcChangeUniverse cu
left outer join gcbondrelationship br
on cu.instrumentid=br.RelatedObjectid
left outer join gcdepositoryeligibilityxref de
on br.relationobjectID=de.orgpermid
left outer join dbo.gcManagerAgent ma
on br.RelationObjectID=ma.orgpermid
where RelationshipType='IsClearingAgentOf'
)
select *
From gcChangeUniverse C
inner Join ID
on ID.InstrumentID = C.InstrumentID
where ID.instrumentid=44654273725

InstrumentID         InstrumentID         RelationshipType                                   DsValue DSOrgCode
-------------------- -------------------- -------------------------------------------------- ------- ---------
44654273725          44654273725          IsClearingAgentOf                                  CMU     NULL
44654273725          44654273725          IsClearingAgentOf                                  CCS     NULL

I  want something like this.

InstrumentID         InstrumentID         RelationshipType                                   DsValue  DsValue2  DSOrgCode
-------------------- -------------------- -------------------------------------------------- -------  -------   ---------
44654273725          44654273725          IsClearingAgentOf                                  CMU      CCS       NULL

There are over 70 possible DsValues in the relevant table, so I don’t think I could use a pivot table unless the columns were limited to the two DsValues above.
Do you have any suggestions?
AlHal2Asked:
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.

lcohanDatabase AnalystCommented:
You mean something like this?
You could also have a look at: https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ and http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

create table #temp
(
    date_crt datetime,
    some_str varchar(3),
    amount money
)

insert into #temp values ('1/1/2015', 'ABC', 1000.00);
insert into #temp values ('2/1/2015', 'DEF', 500.00);
insert into #temp values ('2/1/2015', 'GHI', 800.00);
insert into #temp values ('2/10/2015', 'DEF', 700.00);
insert into #temp values ('3/1/2015', 'ABC', 1100.00);


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.some_str)
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT date_crt, ' + @cols + ' from
            (
                select date_crt
                    , amount
                    , some_str
                from #temp
           ) x
            pivot
            (
                 max(amount)
                for some_str in (' + @cols + ')
            ) p '


execute(@query)

drop table #temp
AlHal2Author Commented:
Thanks for this.  It would put the dsvalues as column names which I don't want.
What about doing a second join between the gcbondrelationship table and the gcdepositoryeligibilityxref table.
Here are some of the columns.

CREATE TABLE [dbo].[GcBondRelationship](
      [RelatedObjectId] [bigint] NOT NULL,
      [RelationObjectId] [bigint] NOT NULL,
      [RelationshipType] [varchar](50) NOT NULL,
      [EffectiveFrom] [datetime] NOT NULL,
      [EffectiveTo] [datetime] NULL,
      [RelatedObjectType] [varchar](50) NULL,
      [RelationObjectType] [varchar](50) NULL,
      [RelationObjectCode] [varchar](32) NULL

CREATE TABLE [dbo].[GcDepositoryEligibilityXref](
      [OrgPermId] [bigint] NOT NULL,
      [RelationObjectCode] [bigint] NULL,
      [DsValue] [char](3) NOT NULL,
      [EJVClearingDescription] [varchar](100) NULL

When I run these select statements there are two matches on two lines.  I want both matches on one line.

select relatedobjectid,relationobjectid,relationobjectcode
from govcorpbondrelationship
where relatedobjectid=44654273725
and relationshiptype='isclearingagentof'

select orgpermid,relationobjectcode,dsvalue
from govcorpdepositoryeligibilityxref
where orgpermid in (5000457298,5000928820)

relatedobjectid      relationobjectid     relationobjectcode
-------------------- -------------------- --------------------------------
44654273725          5000457298           106096733
44654273725          5000928820           106096786

(2 row(s) affected)

orgpermid            relationobjectcode   dsvalue
-------------------- -------------------- -------
5000457298           106096733            CMU
5000928820           106096786            CCS
AlHal2Author Commented:
I solved this myself by joining to the table twice.

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
AlHal2Author Commented:
It worked.
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.