CODEWITHSUNDEEP
mysqlsqldatabase
Manish Patel
Manish Patel

Reputation: 157

How to make table Row Values as a column

I wants to make column name from Row values in Mysql Table

Id  Value               Type
1   ABC Bank        Bank
1   User                 Name
1   123               ID
1   [email protected]       Email
1   Banking             Reference

I want above to be Show like below , can you please help me to write the Query for this. Thanks in Advance

Id      Bank     Name         ID           Email        Reference
1     ABC Bank   User        123    [email protected]   Banking

Upvotes: 1

Views: 540

Answers (2)

JustFanOfYou
JustFanOfYou

Reputation: 21

We can achieve the using dynamically with PIVOT concept, but whatever [Type] field data should not actual field name, it should be different

CREATE TABLE PivotTable (Id INT,Value VARCHAR(100),[Type] VARCHAR(100))

INSERT INTO PivotTable VALUES 
 (1,'ABC Bank','Bank')      
,(1,'User','Name')          
,(1,'123','IDs')                
,(1,'[email protected]','Email')
,(1,'Banking','Reference')

SELECT * FROM PivotTable

DECLARE @Columns NVARCHAR(MAX),
        @SQL NVARCHAR(MAX)

SET @Columns = STUFF((SELECT  DISTINCT ','+[Type] FROM PivotTable FOR XML PATH('')),1,1,'')

SET @SQL = 'SELECT Id,'+@Columns+' FROM 
            (
            SELECT Id,
                   Value,
                   [Type]

            FROM PivotTable
            ) P
            PIVOT
            (
            MAX(Value) FOR [Type] IN ('+@Columns+')
            )pvt'

            EXEC (@SQL)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use conditional aggregation:

select id,
       max(case when type = 'Bank' then value end) as bank,
       max(case when type = 'Name' then value end) as name,
       max(case when type = 'Id' then value end) as otherId,
       max(case when type = 'Email' then value end) as email,
       max(case when type = 'reference' then value end) as reference
from t
group by id;

Upvotes: 1

Related Questions