0

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;
  • What error are you getting? Feel free to add some DML to your post so users can easily start testing on your data – Yannick Liekens Dec 19 '19 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 '19 at 9:11
  • Query works on my machine. With your input, it gives you the output you're asking for. Tested on MSSQL2016 – Yannick Liekens Dec 19 '19 at 9:15
  • You can add a simple where clause. WHERE UserId = x or User_id IN (x,y). – Yannick Liekens Dec 19 '19 at 9:23
  • 1
    If you found a solution you should probably post it as an answer, not part of the question. – mustaccio Dec 19 '19 at 14:45
0

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.

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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