CODEWITHSUNDEEP
sqlsql-server-2008t-sqlpivot
Jorge
Jorge

Reputation: 18247

How can I Pivot this Rows

My basic query 

 ID           Days      Y   Type
3014    L;M;M;J;V;S;D   15  PDC
3014    L;M;M;J;V;S;D   16  PDC
3014    NULL            17  PDC
3014    NULL            18  PDC
3014    NULL            19  PDC
3014    NULL            20  Altern
3014    NULL            21  Altern

What i'm trying to achieve 

3014 L;M;M;J;V;S;D L;M;M;J;V;S;D NULL NULL NULL NULL 15 16 17

My Sql 

    select * from (select 
    FS.FieldStudyId,
    C.Day as Dayss,
    C.IDCourse,
    C.Type
from 
    FieldStudy FS,
    Course C  
where 
    Fs.FieldStudyId = C.FieldStudyId)d
pivot
(
  max(Dayss)
  for FieldStudyId in (select z.FieldStudyId from FieldStudy z) 
)x;

But I doesn't work 

Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 14 Incorrect syntax near ')'

Upvotes: 0

Views: 56

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

SQL Server does not allow subqueries in the PIVOT clause. You will have to employ dynamic SQL, or list them explicitly (static list).

e.g.

declare @sql nvarchar(max);
select @sql = isnull(@sql + ',', '') + quotename(FieldStudyId)
from FieldStudy

set @sql = '
select *
from (
  select 
      FS.FieldStudyId,
      C.Day as Dayss,
      C.IDCourse,
      C.Type
  from 
      FieldStudy FS,
      Course C  
  where 
      Fs.FieldStudyId = C.FieldStudyId)d
pivot
(
  max(Dayss)
  for FieldStudyId in (' + @sql + ') 
)x;';
exec (@sql);

Although this shows you how to use a dynamic list of PIVOT columns, it doesn't produce the answer in your question because the question is not clear at all. With a slight variation to pivot on the IDCourse values instead:

declare @sql nvarchar(max);
select @sql = isnull(@sql + ',', '') + quotename(IdCourse)
from Course;

--select @sql;

set @sql = '
select *
from (
  select 
      FS.FieldStudyId,
      C.Day as Dayss,
      C.IDCourse
  from 
      FieldStudy FS,
      Course C  
  where 
      Fs.FieldStudyId = C.FieldStudyId)d
pivot
(
  max(Dayss)
  for IdCourse in (' + @sql + ') 
)x;';
exec (@sql);

You can get something like the below:

| FIELDSTUDYID |            15 |            16 |     17 |     18 |     19 |     20 |     21 |
---------------------------------------------------------------------------------------------
|         3014 | L;M;M;J;V;S;D | L;M;M;J;V;S;D | (null) | (null) | (null) | (null) | (null) |

But it won't give you the trailing 15...16...17 in your question.

Upvotes: 2

Related Questions