How to convert query results to new column based on ID or specific column?

Question

I have tired everything but I couldn't figure it out. Your help will be greatly appreciated. Bascially, I have four columns: ID, fst_nm, lst_nm, code and I get several duplicate row results because some of the people have more than one Code.

This is that I get:

+------+----------+----------+--------+
| ID   |  fst_nm  |  lst_nm  |   Code |
+------+----------+----------+--------+
| 987  |  BRADLY  |  MARTIN  |  LB102 |
| 987  |  BRADLY  |  MARTIN  |  LB101 |
| 999  |  DAVID   |   LOZA   |  PG102 |
| 921  |  PATRIC  |  TEST    |  P5102 |
| 900  |  JUSTIN  |  MARTIN  |  P6415 |
| 900  |  JUSTIN  |  MARTIN  |  LB105 |
| 900  |  JUSTIN  |  MARTIN  |  P1003 |
| 880  |  ROBIN   |  JURRY   |  90686 |
| 880  |  ROBIN   |  JURRY   |  90471 |
+------+----------+----------+--------+

And this is what I really want:

+------+----------+----------+---------+---------+--------+
| ID   |  fst_nm  |  lst_nm  |  Code   |  Code1  |  Code2 |
+------+----------+----------+---------+---------+--------+
| 987  |  BRADLY  |  MARTIN  |  LB102  |  LB101  |        |
| 999  |  DAVID   |  LOZA    |  PG102  |         |        |
| 921  |  PATRIC  |  TEST    |  P5102  |         |        |
| 900  |  JUSTIN  |  MARTIN  |  P6415  |  LB105  |  P1003 |
| 880  |  ROBIN   |  JURRY   |  90686  |  90471  |        |
+------+----------+----------+---------+---------+--------+

I want to make every row distinct and move Code to new Columns if one person have multiple Codes. Is there any way to do this is sql or MS access? Please help me!

Looks like a pivot/transform in msaccess which allows for dynamic number of columns so pivot on id, fst_nm, lst_nm and then code becomes the # of needed columns. Outside of MSAccess this would have to be done with dynamic SQL using a pivot. or if you have a finite number of codes, a case and max statement witha row number. https://stackoverflow.com/questions/16691853/transform-and-pivot-in-access-2013-sql for msaccess. — xQbert, Aug 07 '17 at 13:33
Which RDBMS are you using? mysql or sql-server or access? Please tag correctly, can't be all of them — Nenad Zivkovic, Aug 07 '17 at 13:33
Please consider if this is what you need. If you need distinct people, consider splitting up in two tables, one for persons and one for code. If not your table must be expanded each time a person gets more than the number of max number of codes for previous persons. This can be done dynamically, but I would not recommend this solution — Søren Kongstad, Aug 07 '17 at 13:34
Do you always have maximum of 3 codes per person, or it can be more? — Nenad Zivkovic, Aug 07 '17 at 13:37
I have removed the tags for every RDBMS. Please retag with the RDBMS you are using as each piece of software has a different implementation of PIVOT like logic. — JNevill, Aug 07 '17 at 13:37
Hi, thank you so much for a quick response. I am looking to do it on both Access query and SQL query. I am not too familiar with Access query so I am trying to do it on SQL server 2012. I am using sql server management studio 2012. The maximum codes per person is 5 but most of them have 2-3 codes. — Milan Pk, Aug 07 '17 at 13:44

Tlivanios · Answer 1 · 2017-08-07T14:32:37.017

Try this

Select distinct id,fst_nm,lst_nm,(Select Code From mytable t2 
Where t1.ID = t2.id limit 1) as Code,
(Select Code From mytable t2 Where t1.ID = t2.id limit 1,1) as Code2,
(Select Code From mytable t2 Where t1.ID = t2.id limit 2,1) as Code3
From mytable t1

If you are using MSSQL Server there is a more tricky situation

Select distinct id,fst_nm,lst_nm,
(select top 1 Code From mytable t2 Where t1.ID = t2.Id order by Code),
(SELECT CODE FROM (
        SELECT 
        CODE,ID, ROW_NUMBER() OVER(ORDER BY CODE) AS ROW
        FROM mytable t2 Where t1.ID = t2.id
        ) AS tbl 
        WHERE ROW = 2
) SECONDCODE,
(SELECT CODE FROM (
        SELECT 
        CODE,ID, ROW_NUMBER() OVER(ORDER BY CODE) AS ROW
        FROM mytable t2 Where t1.ID = t2.id
        ) AS tbl 
        WHERE ROW = 3
) THIRDCODE
From mytable t1

score 0 · Answer 2 · answered Aug 07 '17 at 13:38

0

you can pivot as below:

Select * from (
    Select *, RowN = Row_Number() over (partition by id order by Code) from #data ) a
pivot(max(code) for RowN in ([1],[2],[3])) p

answered Aug 07 '17 at 13:38

Kannan Kandasamy

13,405
3
25
38

How to convert query results to new column based on ID or specific column?

2 Answers2