How to query selected multiple rows into multiple colums

Question

Post reply

How to query selected multiple rows into multiple colums

harrytiadi

SSC Enthusiast

Points: 113
More actions
January 8, 2024 at 8:59 am

#4358387

How to display datas using query data from multiple specific rows in one table sql into multiple columns? For example, insert red data into column1, insert blue data into column2, insert green data into column3, etc.
I've used these code : SELECT um_value FROM wp_um_metadata WHERE um_key='first_name'
But the result I only got one column.
Please help if someone has expert knowledge about MySQL here. Thanks for sharing it.
Here the screenshot of example sql database :

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

First, please note that this is a SQL Server forum, not MySQL.

In T-SQL, I'd do something like this

DROP TABLE IF EXISTS #wp_um_metadata;

CREATE TABLE #wp_um_metadata
(
    user_id INT
   ,um_key VARCHAR(50)
   ,um_value VARCHAR(200)
);

INSERT #wp_um_metadata
(
    user_id
   ,um_key
   ,um_value
)
VALUES
(1, 'whatsapp_number', '123456')
,(1, 'first_name', 'Phil');

SELECT *
FROM #wp_um_metadata wum;

SELECT wum.user_id
      ,WhatsAppNumber = MAX (IIF(wum.um_key = 'whatsapp_number', wum.um_value, NULL))
      ,FirstName = MAX (IIF(wum.um_key = 'first_name', wum.um_value, NULL))
FROM #wp_um_metadata wum
GROUP BY wum.user_id;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

harrytiadi SSC Enthusiast Points: 113 More actions · Answer 2

Thank you, sorry i dont know the different between sql server and mysql. I'm newbie here... 😉

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

harrytiadi wrote:

Thank you, sorry i dont know the different between sql server and mysql. I'm newbie here... 😉

Then perhaps you will find this informative: https://aws.amazon.com/compare/the-difference-between-sql-and-mysql/

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel