SQL convert row into column

Question

we are working on task and there holidays projects.

Table Result is :

            Rn  TicketNo    task                Assigned    emp
            1   a           Holdiay             sunday      emp_A
            2   a           Task A completed    monday      emp_A
            1   b           Holiday             sunday      emp_B
            2   b           Task A completed    monday      emp_B

I want following result.

            TicketNo    emp     sunday   monday
            a          emp_A    Holdiay  Task A completed
            b          emp_B    Holiday  Task A completed

i am trying the query without using pivot. thannks for help.

Are you using MySQL or Oracle? Don't tag products not involved... — jarlh, Nov 17 '16 at 08:30
Refer this Question http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Piyush Gupta, Nov 17 '16 at 08:33
@ksu Try this https://sql-compass.blogspot.in/2014/08/transpose-rows-to-columns-without-pivot.html — Piyush Gupta, Nov 17 '16 at 09:07

score 0 · Accepted Answer · answered Nov 17 '16 at 08:57

You can convert Rows data to column by using below quries.Try It.

1.using pivot

            select distinct  TicketNo,emp,sunday,monday
            from
            (
              select distinct  TicketNo ,task,  Assigned,emp from <tbl_name>
            ) src
            pivot
            (
              max(task)
              for Assigned in (sunday, monday)
            ) piv

2.Using Joins : ( without Pivot )

            select  a.ticketno,
                     a.emp
                    ,a.task as sunday
                    ,b.task as monday
                     from <tbl_name> a join <tbl_name> b 
                     on a.assigned='sunday' and b.assigned='monday' 
                     and a.ticketno=b.ticketno

PowerStar · Answer 2 · 2016-11-17T15:54:06.653

I tried it in a different way using sub queries and join. Anyway glad that you already have the answer.

SELECT * INTO YOUR_TABLE  FROM 
(            SELECT 1 AS Rn,'a' AS TicketNo,'Holiday' as task,'sunday' as Assigned,'emp_A' as emp
            UNION ALL
            SELECT 2,'a','Task A completed','monday','emp_A' UNION ALL
            SELECT 1,'b','Holiday','sunday','emp_B' UNION ALL
            SELECT 2,'b','Task A completed','monday','emp_B'
) X

SELECT * FROM YOUR_TABLE


SELECT T1.TicketNo
       ,T1.emp
       ,T1.Sunday
       ,T2.Monday
FROM       
(SELECT TicketNo
       ,emp
       ,CASE WHEN task='Holiday' then 'Holiday' end as Sunday
FROM YOUR_TABLE
WHERE CASE WHEN task='Holiday' then 'Holiday' end IS NOT NULL) T1

JOIN 

(SELECT TicketNo
       ,emp
       ,CASE WHEN task='Task A completed' then 'Task A completed' end as Monday
FROM YOUR_TABLE
WHERE CASE WHEN task='Task A completed' then 'Task A completed' end IS NOT NULL) T2

ON T1.TicketNo = T2.TicketNo

SQL convert row into column

2 Answers2