How to make table Row Values as a column

Question

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

You should look into making pivots in mysql. Here is a link to a sample pivot : riptutorial.com/mysql/example/10441/creating-a-pivot-query. — camba1
– camba1, Commented Jul 4, 2019 at 2:57
Is the number of values variable per id and is it unknown how many variables there can be and that they can be in any order? — P.Salmon
– P.Salmon, Commented Jul 4, 2019 at 7:47
Number of Values can be vary. it can be anything between 5 and 10 — Manish Patel
– Manish Patel, Commented Jul 5, 2019 at 6:22

Gordon Linoff · Accepted Answer · 2019-07-04 02:50:21Z

1

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;

answered Jul 4, 2019 at 2:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JustFanOfYou · Accepted Answer · 2019-07-04 10:28:35Z

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)

Collectives™ on Stack Overflow

How to make table Row Values as a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related