CODEWITHSUNDEEP
sqlsql-server
Arun
Arun

Reputation: 23

Repeated column values as column header in SQL server

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
---------------------------------------------------

Upvotes: 0

Views: 142

Answers (2)

Gidil
Gidil

Reputation: 4137

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.

Upvotes: 0

Sathish
Sathish

Reputation: 4487

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

Upvotes: 2

Related Questions