I have the following table as Follows:
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!!
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;
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 |
+------------+---------------------+---------------------+
