Pivoting in sql

Question

i have sql table with data as given below

    emp id     empname  dept
    -------    ----     ------
  1              a        Hr
  2              b        Hr
  3              c        Tech
  4              d        Hr
  5              e        Admin
  7              f        Tech
  8              g        Admin

Now i want to pivot the above table get the result like this

                Hr    Tech    Admin
               -----  -----   -----
Empname        a       c       e
Empname        b       f       g
Empname        d

I am just wondering is this even possible using pivoting in sql or there is any other mean to achieve this

Why is `d` not included i n the list? – Felix Pamittan Aug 10 '15 at 11:50 — Felix Pamittan, Aug 10 '15 at 11:50

Felix Pamittan · Accepted Answer · 2015-08-10T12:08:30.497

2

You can use conditional aggregation with ROW_NUMBER:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY dept ORDER BY empid)
    FROM tbl
)
SELECT
    [Hr] = MAX(CASE WHEN dept = 'Hr' THEN empname END),
    [Tech] = MAX(CASE WHEN dept = 'Tech' THEN empname END),
    [Admin] = MAX(CASE WHEN dept = 'Admin' THEN empname END)
FROM cte
GROUP BY rn

Here is the PIVOT version:

SQL Fiddle

;WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY dept ORDER BY empid)
    FROM tbl
)
SELECT *
FROM(
    SELECT
         dept, MAX(empname) AS empname,RN
    FROM Cte
    GROUP BY dept, RN
)t
PIVOT
(
    MAX(empname)
    FOR dept in ([Hr], [Tech], [Admin])
)piv

edited Aug 10 '15 at 12:08

answered Aug 10 '15 at 11:51

Felix Pamittan

31,544
7
41
67

It worked like magic but is it possible using pivot? – abhi Aug 10 '15 at 11:57
Of course, I'll leave it as an exercise to you. Also, I'm not really familiar with `PIVOT`. – Felix Pamittan Aug 10 '15 at 12:06

score 1 · Answer 2 · answered Aug 10 '15 at 11:50

This type of manipulation is often best done in the presentation layer. It appears that you want three different lists in the columns, so the rows are not really related to each other.

That said, you can do this in SQL, but you need to get a "list position" for each column. For that, use row_number():

select h.empname as hr, t.empname as tech, a.empname as admin
from (select empname, row_number() over (order by empname) as seqnum
      from table
      where dept = 'Hr'
     ) h full outer join
     (select empname, row_number() over (order by empname) as seqnum
      from table
      where dept = 'Tech'
     ) t
     on t.seqnum = h.seqnum full outer join
     (select empname, row_number() over (order by empname) as seqnum
      from table
      where dept = 'Admin'
     ) a
     on a.seqnum in (h.seqnum, t.seqnum)

It worked and so does the query of Felix Pamittan, but is it achievable using pivot — abhi, Aug 10 '15 at 11:58
@abhi . . . I actually like Felix's answer better. But I'm leaving this in case it helps anyone else who might stumble on this question. — Gordon Linoff, Aug 10 '15 at 12:03

Pivoting in sql

2 Answers2