ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 274.6k

How to make group by Revision_ID and when repeated display l

Jan 18 2020 6:17 AM
problem
How to make group by Revision_ID and when repeated display last check date separated by stick | ?
I need to group data by Revision_ID that make count to all zpartid
every revision_ID Have group of parts .
and when revision id repeated two time then lastcheckdate is firstdate | seconddate
and if more than two time then display text multi date
so How to do that please ?
  1. CREATE TABLE [Parts].[LifeCycleMaster](  
  2.     [LifeCycleID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
  3.     [ZPartID] [bigintNOT NULL,  
  4.     [LastCheckDate] [dateNULL,  
  5.     [Revision_ID] [bigintNULL,  
  6.  CONSTRAINT [PK_LifeCycleMaster_LifeCycleID] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [LifeCycleID] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY],  
  10.  CONSTRAINT [UK_PartID] UNIQUE NONCLUSTERED   
  11. (  
  12.     [ZPartID] ASC  
  13. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  14. ON [PRIMARY]  
  15. Revision_ID ZPartID LastCheckDate  
  16. 12            10        12/12/2015  
  17. 15            120       12/01/2014  
  18. 15            130       05/05/2016  
  19. 20            170       09/03/2013  
  20. 20            200       09/05/2016  
  21. 20            300       09/08/2017  
  22.           
  23.     FinalResult   
  24. Revision_ID  CountParts LastCheckDate  
  25. 12         1    12/12/2015  
  26. 15         2    05/05/2016 |12/01/2014  
  27. 20         3    Multi date  
 

Answers (6)