convert row into column (SQL Query)

BTrees · Jul 22, 2018

Hello,
I have following data

EmpID Empdept Name
1 dept1
1 dept 2
2 dept1
2 dept2

reguired output
EmpID EmptdeptA EmptdeptB
1 dept1 dept2
2 dept1 dept2

can any one suggest how can i get this?

thanks

fredericofonseca · Jul 22, 2018

of the results you found while searching google which ones did you try and why aren't you using them?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

Olaf Doschke · Jul 22, 2018

This kind of questions always makes me ask why you already want to transform the data with a query.
There are many ways to take the data in its list form as you have it and visually represent it in a way to get the overview you want to have
1.1 A Treeview can give you root nodes per employee and nodes showing in which department he works.
1.2 vice versa have department root nodes and in the child-nodes display the list of employees.
2.1 Use a report grouping data by employees
2.2 Or a report grouping by department
3.1 query the data as is and populate an excel sheet taking employees as rows and store departments as columns.
3.2 likewise with departments as rows.

Especially the third solution would just iterate all the rows in EmpID order Departmentname secondary order and when a row has the same EmpID as before populate the next excel column instead of row. That's the easiest way to do that. So if I was the developer and you gave me the task to get this overview, I'd solve it that way, not already on the SQL level. That's not what SQL is there for.

It's not the job or a query to visualize data, that's the job of a frontend UI or reports or a data-sheet, not a table aka query result.

This is the HTML solution, and while it looks like much more code than a query most of it is a necessary skeleton, the number of lines is not the number of lines written...

Code:

<!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">

  <title>Employee Department List</title>
<style>
#empdep {
    font-family: Arial, Helvetica, sans-serif;
    border-collapse: collapse;
    width: 100%;
}

#empdep td, #empdep th {
    border: 1px solid #ddd;
    padding: 8px;
}

#empdep tr:nth-child(even){background-color: #eee;}

#empdep tr:hover {background-color: #ddd;}

#empdep th {
    padding-top: 12px;
    padding-bottom: 12px;
    text-align: left;
    background-color: lightblue;
}
</style>  
</head>

<body>
<table id="empdep"></table>
<script >

   var data = [
   {"empid":1,"deptname":"dept1"},
   {"empid":1,"deptname":"dept2"},
   {"empid":2,"deptname":"dept1"},
   {"empid":2,"deptname":"dept2"},
   {"empid":3,"deptname":"dept3"}
   ];

var tab, headerrow
tab = document.getElementById('empdep');
headerrow= tab.appendChild(document.createElement('tr'));
header = headerrow.appendChild(document.createElement('th'));
header.innerHTML = 'Employee';

var row,maxdep=0,emp=0,dep=0
data.forEach(function(e){ 
 if (e.empid!==emp)
 {
  row = tab.appendChild(document.createElement('tr'));
  emp = e.empid;
  dep=0
  cell = row.appendChild(document.createElement('td'));
  cell.innerHTML = emp;
 }
 cell = row.appendChild(document.createElement('td'));
 cell.innerHTML = e.deptname;
 if (++dep>maxdep) {
    maxdep=dep;
    header = headerrow.appendChild(document.createElement('th'));
	header.innerHTML = maxdep+'. department';
 } 
});
   
</script>  
</body>
</html>

The JSON used here as data actually can be generated by a simple [tt]SELECT *[/tt] query of your data as done here:

Code:

Declare @empdep as Table (empid int, deptname varchar(20));
Insert into @empdep values (1,'dept1'),(1,'dept2'),(2,'dept1'),(2,'dept2'),(3,'dpet3')

[b]Select * from @empdep for json path[/b]

Which just shows SQL Server can output JSON as needed by a JS frontend but isn't generally needed, in general, the even simpler query needed is just [tt]SELECT * FROM YOURTABLE[/tt]

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

BTrees · Jul 22, 2018

Thanks Olaf, I am looking for the solution to incorporte one of the columns of my ssrs report. I need a sql dynamic code to pull data and display in columns. All the solutions i found so far suggest only hardcode values not dynamic. thanks

mikrom · Jul 22, 2018

Hi BTrees,
I'm not sure if every EMPID should have only 2 EMPDEPTs ?
If so I tried the following (because I don't have MS SQL server, I tried it with DB2):

1. I created MYTABLE and inserted some data:

Code:

select * from mytable

with the result

Code:

EMPID  EMPDEPT
   1    dept1 
   1    dept2 
   2    dept1 
   2    dept2

2. then I created the temporary table TEMP_NUMBERED (using CTE = Common Table Expressions) where the two departments are numbered:

Code:

with temp (empdept) as (             
  select                             
    distinct empdept                 
  from mytable                       
),                                   
temp_numbered (empdept, empid) as (  
  select                             
    empdept, rownumber() over() empid
  from temp                          
)                                    
select                               
  *                                  
from temp_numbered

with the result

Code:

EMPDEPT                      EMPID
 dept2                           1
 dept1                           2

3. Finally I have done an inner join of MYTABLE and TEMP_NUMBERED:

Code:

with temp (empdept) as (                         
  select                                         
    distinct empdept                             
  from mytable                                   
),                                               
temp_numbered (empdept, empid) as (              
  select                                         
    empdept, rownumber() over() empid            
  from temp                                      
)                                                
select                                           
  t1.empid, t1.empdept, t2.empdept               
from mytable t1 inner join                       
     temp_numbered t2 on t1.empid = t2.empid and 
                         t1.empdept != t2.empdept

and got this result:

Code:

EMPID  EMPDEPT  EMPDEPT
   1    dept1    dept2 
   2    dept2    dept1

Olaf Doschke · Jul 22, 2018

Take a look at this about grouping data in SSRS with the Matrix control and drill down:

https://www.red-gate.com/simple-tal...ing-services-basics-customizing-ssrs-reports/

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

mikrom · Jul 23, 2018

In my previous post I maked stupid assumption that EMPID would be equal to the numbering of EMPDEPTs which I got using ROWNUMBER() and I maked join based on it.

mikrom · Jul 23, 2018

IMHO, the simplest way would be write a short program which does it for you, but if you cannot do it or you need a query you can write an UDTF (User Defined Table Function).
As proof of concept I tried this:

Code:

[COLOR=#804040][b]create[/b][/color] [COLOR=#804040][b]or[/b][/color] replace [COLOR=#6a5acd]function[/color] MYFUNCTION ()
returns [COLOR=#6a5acd]table[/color](
  EMPID    [COLOR=#6a5acd]DECIMAL[/color] ([COLOR=#ff00ff]3[/color],[COLOR=#ff00ff]0[/color]),                    
  EMPDEPTA [COLOR=#2e8b57][b]CHAR[/b][/color]([COLOR=#ff00ff]10[/color]),
  EMPDEPTB [COLOR=#2e8b57][b]CHAR[/b][/color]([COLOR=#ff00ff]10[/color])
)
language SQL
specific MYUDTF01
modifies SQL data
no external action
deterministic
disallow parallel

[COLOR=#6a5acd]begin[/color]
  declare FETCH_NR  INT [COLOR=#6a5acd]default[/color] [COLOR=#ff00ff]0[/color];
  declare MY_ID     [COLOR=#6a5acd]decimal[/color] ([COLOR=#ff00ff]3[/color],[COLOR=#ff00ff]0[/color]);                    
  declare MY_DEPT   [COLOR=#2e8b57][b]char[/b][/color]([COLOR=#ff00ff]10[/color]);
  declare MY_DEPTA  [COLOR=#2e8b57][b]char[/b][/color]([COLOR=#ff00ff]10[/color]);
  declare MY_DEPTB  [COLOR=#2e8b57][b]char[/b][/color]([COLOR=#ff00ff]10[/color]);
  declare EOF INT [COLOR=#6a5acd]default[/color] [COLOR=#ff00ff]0[/color];
  declare C1 [COLOR=#6a5acd]cursor[/color] [COLOR=#6a5acd]for[/color]
    [COLOR=#804040][b]select[/b][/color] 
      EMPID, EMPDEPT
    [COLOR=#6a5acd]from[/color] 
      MYTABLE
    [COLOR=#6a5acd]order[/color] [COLOR=#6a5acd]by[/color] EMPID, EMPDEPT
  ;
  declare continue handler [COLOR=#6a5acd]for[/color] [COLOR=#804040][b]not[/b][/color] found
        [COLOR=#804040][b]set[/b][/color] EOF = [COLOR=#ff00ff]1[/color];
 
  [COLOR=#0000ff]-- temporary table in QTEMP      [/color]
  [COLOR=#804040][b]create[/b][/color] [COLOR=#804040][b]or[/b][/color] replace [COLOR=#6a5acd]table[/color] QTEMP.RESULTTAB (
    EMPID    [COLOR=#6a5acd]DECIMAL[/color] ([COLOR=#ff00ff]3[/color],[COLOR=#ff00ff]0[/color]),                    
    EMPDEPTA [COLOR=#2e8b57][b]CHAR[/b][/color]([COLOR=#ff00ff]10[/color]),
    EMPDEPTB [COLOR=#2e8b57][b]CHAR[/b][/color]([COLOR=#ff00ff]10[/color])
  );
  [COLOR=#0000ff]-- empty temporary table[/color]
  [COLOR=#804040][b]delete[/b][/color] [COLOR=#6a5acd]from[/color] QTEMP.RESULTTAB;

  open C1;
  while EOF = [COLOR=#ff00ff]0[/color] do
    [COLOR=#804040][b]set[/b][/color] FETCH_NR = FETCH_NR + [COLOR=#ff00ff]1[/color];
    fetch C1 [COLOR=#6a5acd]into[/color] MY_ID, MY_DEPT;
    [COLOR=#6a5acd]if[/color] MOD(FETCH_NR, [COLOR=#ff00ff]2[/color]) != [COLOR=#ff00ff]0[/color] [COLOR=#6a5acd]then[/color]
      [COLOR=#0000ff]-- odd fetch[/color]
      [COLOR=#804040][b]set[/b][/color] MY_DEPTA = MY_DEPT;
    [COLOR=#6a5acd]else[/color] 
      [COLOR=#0000ff]-- on the even fetch do following[/color]
      [COLOR=#804040][b]set[/b][/color] MY_DEPTB = MY_DEPT;
      [COLOR=#804040][b]insert[/b][/color] [COLOR=#6a5acd]into[/color] QTEMP.RESULTTAB [COLOR=#6a5acd]values[/color](MY_ID, MY_DEPTA, MY_DEPTB); 
    [COLOR=#6a5acd]end[/color] [COLOR=#6a5acd]if[/color];
  [COLOR=#6a5acd]end[/color] while;
  close C1;
  [COLOR=#6a5acd]return[/color]
    [COLOR=#804040][b]select[/b][/color] EMPID, EMPDEPTA, EMPDEPTB [COLOR=#6a5acd]from[/color] QTEMP.RESULTTAB;
[COLOR=#6a5acd]end[/color]
;

Now when I have the following data in MYTABLE

Code:

EMPID  EMPDEPT   
   1   dept1     
   1   dept2     
   2   dept1     
   2   dept2     
  10   deptX     
  10   deptY     
  20   deptA     
  20   deptB     
 100   dept100-01
 100   dept100-02
 200   dept200A  
 200   dept200B

using the UDTF MYFUNCTION()

Code:

select * from table(MYFUNCTION()) as T

delivers following result

Code:

EMPID  EMPDEPTA    EMPDEPTB  
   1   dept1       dept2     
   2   dept1       dept2     
  10   deptX       deptY     
  20   deptA       deptB     
 100   dept100-01  dept100-02
 200   dept200A    dept200B

But as I mentioned earlier, I only have DB2, so I'm not sure what's possible with MS SQL ...

BTrees · Jul 23, 2018

Thanks all for great posts. I am trying different solutions as my query is pretty complex. Will share my experience than.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

convert row into column (SQL Query)

BTrees

IS-IT--Management

fredericofonseca

IS-IT--Management

Olaf Doschke

Programmer

BTrees

IS-IT--Management

mikrom

Programmer

Olaf Doschke

Programmer

mikrom

Programmer

mikrom

Programmer

BTrees

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor