Convert rows to column for every nth row by ID

Question

I'm trying to convert rows to columns.

I have the following columns: user_id, meta_key, meta_value

I have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'

Right now I can generate the rows and columns sorted and from a specific user_id. In my case it's from 15. This is with the following query :

SELECT user_id, meta_key, meta_value 
FROM DATA_A ORDER BY user_id DESC; 
where meta_key IN ('first_name', 'last_name', 'stillingsbetegnelse') 
AND user_id > 15;

And the result is:

user_id     meta_key                meta_value
16          first_name              Laxgdfds
16          last_name               Narentdsaa
16          stillingsbetegnelse     Andet
18          first_name              Riedsa
18          last_name               Joe
18          stillingsbetegnelse     Andet

(all information is anonymized)

How do I get the following output?

first_name    last_name         stillingsbetegnelse
Laxgdfds      Narentdsaa        Andet
Riedsa        Joe               Andet

I have tried methods with pivot, but I'm getting an error:

    select
        p.*
    from DATA_A
    pivot(
        max([meta_value])
        for [meta_key] in ([first_name], [last_name], [stillingsbetegnelse])
) as p

I think the problem is that I want to take values only from a certain ID. How can I implement this feature? I only want the states data above, but I'm getting a lot of other data, that I don't have any use for. ...........................

With help i found a solution from Stackoverflow. The solution is

> SELECT user_id,    MAX(IF(meta_key = 'first_name', meta_value,NULL))
> AS Fornavn,    MAX(IF(meta_key = 'last_name', meta_value,NULL)) AS
> Efternavn,    MAX(IF(meta_key = 'stillingsbetegnelse',
> meta_value,NULL)) AS  FROM DATA_A GROUP BY user_id;

Answer 1

Here's the code I ran to simulate the problem you had and see what the error message was and what to do, however when I ran it - it ran successfully.

CREATE TABLE #TestTable
(   [user_id]   varchar(50) NULL
,   [meta_key]  varchar(50) NULL
,   [meta_value]    VARCHAR(50) NULL
)

INSERT INTO #TestTable
VALUES  ('16'   , 'first_name'          , 'Laxgdfds'    )
    ,   ('16'   , 'last_name'           , 'Narentdsaa'  )
    ,   ('16'   , 'stillingsbetegnelse' , 'Andet'       )
    ,   ('18'   , 'first_name'          , 'Riedsa'      )
    ,   ('18'   , 'last_name'           , 'Joe'         )
    ,   ('18'   , 'stillingsbetegnelse' , 'Andet'       )

SELECT  P.*
FROM    #TestTable
PIVOT   (MAX([META_VALUE])
         FOR [META_KEY] IN ([FIRST_NAME], [LAST_NAME], [STILLINGSBETEGNELSE])
        ) AS P

So I would recommend 1) copying and running my code, and if that doesn't work responding back with the error message. 2) If my code works with no error message, then I would recommend reviewing your original code for possible typos.