locked
SQL server - group all data by one column RRS feed

  • Question

  • User1821839919 posted

    I need some help in ms sql stored procedure.all data group by Train_B_N.

    T_id Subs_cd Train_code Trainer Department Duration Date Training_Program Employee_id Train_B_N stat
    1 CGG T1 Fiza HRS 500 2016-07-05 00:00:00.000 I 30100347 1 S_1
    2 CGG T1 Fiza HRS 500 2016-07-05 00:00:00.000 I 30100342 1 S_1
    8 CGG T1 Fizal HRS 480 2016-07-05 00:00:00.000 I 20200339 6 S_1
    9 CGG T1 Fizal HRS 480 2016-07-05 00:00:00.000 I 20200340 6 S_1
    10 CGG T1 Fizal HRS 480 2016-07-05 00:00:00.000 I 20100411 6 S_1

    Expected result : 

    Subs_cd Train_code Trainer Department Duration Date Training_Program Train_B_N stat
    CGG T1 Fiza HRS 500 2016-07-05 00:00:00.000 I 1 S_1
    CGG T1 Fizal HRS 480 2016-07-05 00:00:00.000 I 6 S_1
    Tuesday, July 5, 2016 3:14 AM

Answers

  • User-271186128 posted

    Hi kiang_yit,

    According to your description, I suggest you could use the following SQL statement:

    WITH cte AS
    (
       SELECT *,
             ROW_NUMBER() OVER (PARTITION BY Train_B_N ORDER BY T_id DESC) AS rn
       FROM Table3
    )
    SELECT *
    FROM cte
    WHERE rn = 1

    The output as below:

    T_id	Subs_cd	Train_code	Trainer	Department	Duration	Date	  Training_Program	Employee_id	Train_B_N	stat	rn
    2	CGG	T1	         Fiza	 HRSB	        500	2016-07-05 00:00:00.000	I	        30100342	1	        S_1	1
    10	CGG	T1	         Fizal	 HRSE	        480	2016-07-05 00:00:00.000	I	        20100411	6	        S_1	1

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2016 10:42 AM

All replies

  • User177399542 posted

    Check this:

    DECLARE @testTable TABLE
    (
    T_id 	int,
    Subs_cd varchar(50),
    Train_code 	varchar(50),
    Trainer 	varchar(50),
    Department 	varchar(50),
    Duration 	INT,
    Date 	datetime,
    Training_Program varchar(50),
    Employee_id varchar(50),	
    Train_B_N 	INT,
    stat varchar(50)
    )
    
    INSERT INTO @testTable VALUES
    (1,'CGG','T1','Fiza','HRS',500,'2016-07-05 00:00:00.000','I',30100347,1,'S_1')
    INSERT INTO @testTable VALUES
    (2,'CGG','T1','Fiza','HRS',500,'2016-07-05 00:00:00.000','I',30100342,1,'S_1')
    INSERT INTO @testTable VALUES(8,'CGG,','T1','Fizal','HRS',480,'2016-07-05 00:00:00.000','I',20200339,6,'S_1' )
    INSERT INTO @testTable VALUES(9,'CGG,','T1','Fizal','HRS',480,'2016-07-05 00:00:00.000','I',20200340,6,'S_1' )
    INSERT INTO @testTable VALUES(10,'CGG,','T1','Fizal','HRS',480,'2016-07-05 00:00:00.000','I',20100411,6,'S_1')
    
    SELECT 
    MAX(T_id) AS T_id,MAX(Subs_cd) AS Subs_cd,MAX(Train_code) AS Train_code,MAX(Trainer) AS Trainer,
    MAX(Department) AS Department,MAX(Duration) AS Duration,MAX(DATE) AS DATE,MAX(Training_Program) AS Training_Program
    ,MAX(Train_B_N) AS Train_B_N,MAX(stat) AS stat
     FROM @testTable GROUP BY Train_B_N

    Tuesday, July 5, 2016 4:13 AM
  • User77042963 posted
    SELECT DISTINCT Subs_cd,
                    Train_code,
                    Trainer,
                    Department,
                    Duration,
                    Date,
                    Training_Program,
                    Train_B_N,
                    stat
    FROM   yourtable 

    Wednesday, July 6, 2016 3:40 PM
  • User-271186128 posted

    Hi kiang_yit,

    According to your description, I suggest you could use the following SQL statement:

    WITH cte AS
    (
       SELECT *,
             ROW_NUMBER() OVER (PARTITION BY Train_B_N ORDER BY T_id DESC) AS rn
       FROM Table3
    )
    SELECT *
    FROM cte
    WHERE rn = 1

    The output as below:

    T_id	Subs_cd	Train_code	Trainer	Department	Duration	Date	  Training_Program	Employee_id	Train_B_N	stat	rn
    2	CGG	T1	         Fiza	 HRSB	        500	2016-07-05 00:00:00.000	I	        30100342	1	        S_1	1
    10	CGG	T1	         Fizal	 HRSE	        480	2016-07-05 00:00:00.000	I	        20100411	6	        S_1	1

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2016 10:42 AM