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 
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:

    from DATA_A
        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;
  • What error are you getting? Feel free to add some DML to your post so users can easily start testing on your data Dec 19, 2019 at 9:00
  • 1) Does the list of meta_key values is static? 2) Do NOT use the asterisk in pivot query. 3) Use FROM (SELECT * FROM DATA_A) src - it seems this table contains excess fields. See FROM - Using PIVOT and UNPIVOT, especially the examples.
    – Akina
    Dec 19, 2019 at 9:11
  • Query works on my machine. With your input, it gives you the output you're asking for. Tested on MSSQL2016 Dec 19, 2019 at 9:15
  • You can add a simple where clause. WHERE UserId = x or User_id IN (x,y). Dec 19, 2019 at 9:23
  • 2
    If you found a solution you should probably post it as an answer, not part of the question.
    – mustaccio
    Dec 19, 2019 at 14:45

1 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.

(   [user_id]   varchar(50) NULL
,   [meta_key]  varchar(50) NULL
,   [meta_value]    VARCHAR(50) NULL

VALUES  ('16'   , 'first_name'          , 'Laxgdfds'    )
    ,   ('16'   , 'last_name'           , 'Narentdsaa'  )
    ,   ('16'   , 'stillingsbetegnelse' , 'Andet'       )
    ,   ('18'   , 'first_name'          , 'Riedsa'      )
    ,   ('18'   , 'last_name'           , 'Joe'         )
    ,   ('18'   , 'stillingsbetegnelse' , 'Andet'       )

FROM    #TestTable
        ) 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.

Screenshot of me running the code you provided by creating a temp table with the data you gave as an example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.