Piviot table values using sql ,SQLserver 2008r2

declare @T table (ProjectNum int, MilestoneType varchar(100), Edate varchar(10))
insert into @T values
(1, 'ULUP', '2018 Q2'),
(1, 'ULUP', '2018 Q3'),
(1, 'RFP', '2017 Q4'),
(1, 'Contract', '2018 Q3'),
(2, 'Contract', '2017 Q4'),
(2, 'Contract', '2017 Q2'),
(2, 'Closing', '2019 Q1'),
(2, 'Landsale', '2017 Q2'),


using Pivot I could get desired output only if each ProjectNum has only one MilestoneType.
But as you could see one ProjectNum can have multiple Milestone's of same type. in that case i would like to get the one oldest.
Ex: Project 1 has 'ULUP' on '2018 Q2' and '2018' Q3'. I need to pick '2018 Q2'.

How can this be done using Pivot or any other efficiently. (FYI i have like 50 thousand records)

SQLServer 2008 r2

:OUtput:
---------
ProjectNum      ULUP            RFP                  Contract      Closing       Landsale
1                  2018 Q2            2017 Q4            2018 Q3            NA                  NA
2                   NA                  NA                  2017 Q2            2019 Q1            2017 Q2
kishan66Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Could you not take the min of the edate and work on that instead.

Try as follows:

declare @T table (ProjectNum int, MilestoneType varchar(100), Edate varchar(10))
insert into @T values
(1, 'ULUP', '2018 Q2'),
(1, 'ULUP', '2018 Q3'),
(1, 'RFP', '2017 Q4'),
(1, 'Contract', '2018 Q3'),
(2, 'Contract', '2017 Q4'),
(2, 'Contract', '2017 Q2'),
(2, 'Closing', '2019 Q1'),
(2, 'Landsale', '2017 Q2')

declare @T1 table (ProjectNum int, MilestoneType varchar(100), Edate varchar(10))
insert into @T1 
select [ProjectNum], [MilestoneType], min([Edate])
from @T
group by [ProjectNum], [MilestoneType]

select *
from @T1
order by 1, 2

Open in new window


Issue your pivot statement on @T1
Scott PletcherSenior DBACommented:
SELECT T.ProjectNum,
    ISNULL(MIN(CASE WHEN T.MilestoneType = 'ULUP' THEN T.Edate END), 'NA') AS ULUP,
    ISNULL(MIN(CASE WHEN T.MilestoneType = 'RFP' THEN T.Edate END), 'NA') AS RFP,
    ISNULL(MIN(CASE WHEN T.MilestoneType = 'Contract' THEN T.Edate END), 'NA') AS Contract,
    ISNULL(MIN(CASE WHEN T.MilestoneType = 'Closing' THEN T.Edate END), 'NA') AS Closing,
    ISNULL(MIN(CASE WHEN T.MilestoneType = 'Landsale' THEN T.Edate END), 'NA') AS Landsale
FROM @T T
GROUP BY T.ProjectNum
ORDER BY T.ProjectNum
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
it also can be done using Pivot directly:

declare @T table (ProjectNum int, MilestoneType varchar(100), Edate varchar(10))
insert into @T values
(1, 'ULUP', '2018 Q2'),
(1, 'ULUP', '2018 Q3'),
(1, 'RFP', '2017 Q4'),
(1, 'Contract', '2018 Q3'),
(2, 'Contract', '2017 Q4'),
(2, 'Contract', '2017 Q2'),
(2, 'Closing', '2019 Q1'),
(2, 'Landsale', '2017 Q2')

select ProjectNum,
case when ULUP is null then 'NA' else ULUP end ULUP,
case when RFP is null then 'NA' else RFP end RFP,
case when Contract is null then 'NA' else Contract end Contract,
case when Closing is null then 'NA' else Closing end Closing,
case when Landsale is null then 'NA' else Landsale end Landsale
from @T
pivot
(
  min(edate)
  for MilestoneType in (ULUP, RFP, Contract, Closing, Landsale)
) p

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kishan66Author Commented:
Thank you Scott Pletcher & Ryan. your solutions gave me desired output.

Also curious if there is a way to get csv of Edate values(sorted) if there are more than 1 available for a combination of ProjectNum & MilestoneType.

Ex:Below pls look at ProjectNum#1 & MilestoneType#ULUP

ProjectNum                  ULUP                        RFP               Contract                        Closing                  Landsale
1                               2018 Q2,2018 Q3                 2017 Q4            2018 Q3                           NA                           NA
2                                         NA                               NA                  2017 Q2,2017 Q4              2019 Q1                  2017 Q2
kishan66Author Commented:
i think i got it figured out.Thanks for the response & time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.