Convert Each Column Value To Become A Column Name

Question

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.

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table). — Tim Biegeleisen, Oct 13 '19 at 07:00
@TimBiegeleisen my question is for SQL and simpler than the question you referenced. — Farzad Karimi, Oct 13 '19 at 07:12

score 1 · Answer 1 · answered Oct 13 '19 at 06:57

1

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;

answered Oct 13 '19 at 06:57

Lukasz Szozda

162,964
23
234
275

I don't know the values of the 'referenceTitle' column to include them in my query – Farzad Karimi Oct 13 '19 at 06:59
@eFarzad Column metadata has to be known in advance. Otherwise you need to look for 'dynamic pivot' – Lukasz Szozda Oct 13 '19 at 07:01

score 0 · Accepted Answer · answered Oct 13 '19 at 08:38

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

Convert Each Column Value To Become A Column Name

2 Answers2