1

I have the following table as Follows:

enter image description here

I Would like to Convert it as Follows: 

+------------+---------------------+---------------------+
| Child_Code |     SewingStart     |      SewingEnd      |
+------------+---------------------+---------------------+
|     000001 | 2017-02-21 00:00:00 | 2017-03-21 00:00:00 |
+------------+---------------------+---------------------+

Any Help Please!!

TanvirArjel
  • 30,049
  • 14
  • 78
  • 114
  • 3
    Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – Stefano Zanini Mar 21 '17 at 12:00
  • Add some more sample data to make things trickier and more describing. (If it always is two dates per code, you can do a group by.) – jarlh Mar 21 '17 at 12:00

2 Answers2

2

If you have a limited number of rows, you can use conditional aggregation or pivot. But, you need a column for this. So:

select child_code,
       max(case when seqnum = 1 then plan_date end) as plan_date_1,
       max(case when seqnum = 2 then plan_date end) as plan_date_2
from (select t.*,
             row_number() over (partition by child_code order by plan_date) as seqnum
      from t
     ) t
group by child_code;

You can only use this method if you know the maximum number of plan dates that you want. Otherwise, you will need to use a dynamic pivot. The idea is the same, but the query string needs to be constructed and should then be passed to sp_executesql.

EDIT:

If you have only two values, then group by is probably easier. The following handles the case where there is only one value:

select child_code, min(plan_date) as plan_date_1,
       (case when min(plan_date) <> max(plan_date) then max(plan_date)
        end) as plan_date_2
from t
group by child_code;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Wow!! It Works!! No Problem! plan_date is restricted to two!! Dear Gordon Linoff, I need some more help from you!! wait I shall mention you here..Hope You will Help me – TanvirArjel Mar 21 '17 at 12:05
1

You will need to use dynamic sql if the maximum number of plan_date is unknown. You will need to use row_number() to number each list partitioned by Child_Code for use with pivot().

test setup:

create table t (child_code varchar(6), plan_date datetime);
insert into t values ('000001','20170221'),('000001','20170321');
declare @cols nvarchar(max);
declare @sql  nvarchar(max);

  select @cols = stuff((
    select distinct 
      ',' + quotename('Plan_Date_'
          +convert(nvarchar(10),row_number() over (
              partition by Child_Code 
              order by     Plan_Date 
          ))
          )
      from t 
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'');

select @sql = '
 select Child_Code, ' + @cols + '
 from  (
  select 
      Child_Code
    , Plan_Date
    , rn=''Plan_Date_''+convert(nvarchar(10),row_number() over (
          partition by Child_Code 
          order by     Plan_Date 
        ))
  from t
    ) as a
 pivot (max([Plan_Date]) for [rn] in (' + @cols + ') ) p';
 select @sql as CodeGenerated;
 exec sp_executesql @sql;

rextester demo: http://rextester.com/YQCR87525

code generated:

 select Child_Code, [Plan_Date_1],[Plan_Date_2]
 from  (
  select 
      Child_Code
    , Plan_Date
    , rn='Plan_Date_'+convert(nvarchar(10),row_number() over (
          partition by Child_Code 
          order by     Plan_Date 
        ))
  from t
    ) as a
 pivot (max([Plan_Date]) for [rn] in ([Plan_Date_1],[Plan_Date_2]) ) p

returns

+------------+---------------------+---------------------+
| Child_Code |     Plan_Date_1     |     Plan_Date_2     |
+------------+---------------------+---------------------+
|     000001 | 21.02.2017 00:00:00 | 21.03.2017 00:00:00 |
+------------+---------------------+---------------------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59