CODEWITHSUNDEEP
sqlconverters
Farzad Karimi
Farzad Karimi

Reputation: 780

Convert Each Column Value To Become A Column Name

I think my question is very simple and the answer must be simple too. I just want something like a pivot on one of my three columns. I have a query like this :

SELECT AgentId, ReferenceTitle, PaymentValue FROM AgentPayments WHERE PaymentValue > 0

the result will be something like this :

AgentId       ReferenceTitle      PaymentValue     
---------------------------------------------------
AgentId1      ReferenceTitle1     PayementValue1  
AgentId1      ReferenceTitle2     PayementValue2   
AgentId1      ReferenceTitle3     PayementValue3
AgentId2      ReferenceTitle2     PayementValue4
AgentId3      ReferenceTitle2     PayementValue5
AgentId3      ReferenceTitle3     PayementValue6

it returns some records for me as you see, now i want to convert the result in a way that each value of column 'ReferenceTitle' become a column for itself like below :

AgentId     ReferenceTitle1      ReferenceTitle2     ReferenceTitle3            
--------------------------------------------------------------------
AgentId1     PayementValue1      PayementValue2       PayementValue3
AgentId2         NULL            PayementValue4            NULL
AgentId3         NULL            PayementValue5       PayementValue6

I just found very complicated results by searching, but I think there must be a simple solution. do you think like me?

UPDATE

I don't know the values of the 'ReferenceTitle' Column, so I can't use those values in my query.

Upvotes: 0

Views: 44

Answers (2)

Farzad Karimi
Farzad Karimi

Reputation: 780

finally, I reached the answer using dynamic pivot as @lukasz Said, at least it's less complicated as I expected :

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

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME(ReferenceTitle) 
                FROM AgentPayments 
                WHERE PaymentValue > 0 
                GROUP BY ReferenceTitle
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
                ,1,1,'')

SELECT @query = '
SELECT * FROM
(
SELECT   
AgentId, ReferenceTitle, PaymentValue
FROM AgentPayments 
) X
PIVOT 
(
MAX(PaymentValue)
for ReferenceTitle in ('+@cols+')
) P'
EXEC SP_EXECUTESQL @query

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use conditional aggregation:

SELECT AgentId,
 MAX(CASE WHEN ReferenceTitle='ReferenceTitle1' THEN PaymentValue END) AS ReferenceTitle1,
 MAX(CASE WHEN ReferenceTitle='ReferenceTitle2' THEN PaymentValue END) AS ReferenceTitle2,
 MAX(CASE WHEN ReferenceTitle='ReferenceTitle3' THEN PaymentValue END) AS ReferenceTitle3
FROM tab
GROUP BY AgentId;

Upvotes: 1

Related Questions