0

I have a table like this.

--------------------------------
File      File 
Name      Stage    Time
---------------------------------
Arun    Start   01-07-2010
Ajit    Middle  07-01-2010
Paddu   Start   14-12-2010
Manu    End     23-06-2010
Ajit    End     08-01-2010
Paddu   Middle  16-12-2010
Arun    Middle  05-07-2010
Ajit    Archive 12-01-2010
Paddu   End     18-12-2010
Manu    Archive 25-06-2010
Paddu   Archive 20-01-2011
Arun    End     09-11-2010
---------------------------------

I want the output as 

---------------------------------------------------
File 
Name    Start       Middle      End         Archive
---------------------------------------------------
Arun    01-07-2010  NULL        NULL        NULL
Ajit    NULL        07-01-2010  NULL        NULL
Paddu   14-12-2010  NULL        NULL        NULL
Manu    NULL        NULL        23-06-2010  NULL
Ajit    NULL        NULL        08-01-2010  NULL
Paddu   NULL        16-12-2010  NULL        NULL
Arun    02-07-2010  05-07-2010  NULL        NULL
Ajit    NULL        NULL        NULL        12-01-2010
Paddu   NULL        NULL        18-12-2010  NULL
Manu    NULL        NULL        NULL        25-06-2010
Paddu   NULL        NULL        NULL        20-01-2011
Arun    NULL        NULL       09-11-2010   NULL
---------------------------------------------------
TechDo
  • 18,398
  • 3
  • 51
  • 64
Arun
  • 23
  • 4
  • http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server?lq=1 – Pavel Nefyodov Aug 06 '14 at 09:33
  • Welcome to Stack Overflow! We're glad to have you, especially since you obviously took such care to provide a clear, example-filled question. Keep it up! The SQL technique you are looking for is called a PIVOT - there are lots of questions here on similar things, I'd suggest reading this post as a start (http://stackoverflow.com/questions/17073134/sql-server-join-tables-and-pivot). Your question might be closed as a duplicate, since we've already got an answer, but searching for 'SQL PIVOT JOIN' will help in the future. – Pavel Nefyodov Aug 06 '14 at 09:34
  • I am looking to extract the output only from one table and not from multiple table. Hence the question was closed. Anyway thank you for your comments. – Arun Aug 08 '14 at 09:06

2 Answers2

2

try like this

select filename, 
case when FileStage='Start' then FileStage else null end as Start,
case when FileStage='Middle' then FileStage else null end as Middle,
case when FileStage='End' then FileStage else null end as End,
case when FileStage='Archive' then FileStage else null end as Archive from table1
Sathish
  • 4,419
  • 4
  • 30
  • 59
0

I would try something like this:

SELECT T0.[file name], 
       T1.time [Start], 
       T2.time [Middle], 
       T3.time [End], 
       T4.time [Archive] 
FROM   (SELECT DISTINCT [file name] 
        FROM   table1)T0 
       FULL JOIN (SELECT [file name], 
                         time 
                  FROM   table1 
                  WHERE  [file stage] = 'Start')T1 
              ON T0.[file name] = t1.[file name] 
       FULL JOIN (SELECT [file name], 
                         time 
                  FROM   table1 
                  WHERE  [file stage] = 'Middle')T2 
              ON T0.[file name] = T2.[file name] 
       FULL JOIN (SELECT [file name], 
                         time 
                  FROM   table1 
                  WHERE  [file stage] = 'End')T3 
              ON T0.[file name] = T3.[file name] 
       FULL JOIN (SELECT [file name], 
                         time 
                  FROM   table1 
                  WHERE  [file stage] = 'Archive')T4 
              ON T0.[file name] = T4.[file name]

You can play around with a demo on SQL Fiddle.

Gidil
  • 4,137
  • 2
  • 34
  • 50