how to get a result from a query where the first c

问题:

I have a query that returns just a few rows but I need the results grouped in such a way that the first 2 columns do not repeat until their values changes.

Let me show you what I mean that will be easier.
This is the query:

select isnull(d.FirstName, '') + ' ' + isnull(d.LastName, '') as Chauffeur,
       t.securysatnr,
       convert(date, p.PlanningDate) as Datum,
       p.StartTime as LaadUur,
       r.LotNr,
       isnull(pFrom.City, '') + ' - ' + isnull(pTo.City, '') as RitInfo 
from   tblPlanning p
  left outer join vwRit r on p.RitID = r.RitID
  left outer join tblPlace pFrom on r.VertrekID = pFrom.PlaceID
  left outer join tblPlace pTo on r.BestemmingID = pTo.PlaceID
  inner join tblDriver d on p.DriverID = d.DriverID
  inner join tblTruck t on p.TruckID = t.TruckID
where  convert(date, p.PlanningDate) >= convert(date, getdate())
order by Chauffeur, convert(date, p.PlanningDate), p.StartTime

and this is the resultset returned :

Chauffeur   securysatnr     Datum       LaadUur  LotNr    RitInfo   
---------   -----------     -----       -------  -----    -------   
Aloyzyas    JBB017          2017-07-12    13     RT-0181  Creutzwald (Tramosa) - Koln TS    
Aloyzyas    JBB017          2017-07-12    20     EMPTY    Koln TS - Neuss (ATN) 
Aloyzyas    JBB017          2017-07-13     6     2094935  Neuss (ATN) - Zulpich WWL 
Andrii      HT5485 FB-CA    2017-07-12    14     EMPTY    Zulpich WWL - Born (Nedcar)   
Andrii      HT5485 FB-CA    2017-07-12    16     1709426  Born (Nedcar) - Zeebrugge Hanze terminal 521-525  
Andrii      HT5485 FB-CA    2017-07-13     8     0006620  Zeebrugge ICO gate 502 - Niederkorn (Collé)   
Darius      HPV472          2017-07-12    17     0006624  Zeebrugge ICO gate 502 - Antwerpen 1333   
and so on...

And this is the result I would like to get:

Chauffeur   securysatnr    Datum      LaadUur   LotNr     RitInfo   
---------   -----------    -----      -------   -----     -------   
Aloyzyas    JBB017          2017-07-12    13    RT-0181   Creutzwald (Tramosa) - Koln TS    
                            2017-07-12    20    EMPTY     Koln TS - Neuss (ATN) 
                            2017-07-13     6    2094935   Neuss (ATN) - Zulpich WWL 
Andrii      HT5485 FB-CA    2017-07-12    14    EMPTY     Zulpich WWL - Born (Nedcar)   
                            2017-07-12    16    1709426   Born (Nedcar) - Zeebrugge Hanze terminal 521-525  
                            2017-07-13     8    0006620   Zeebrugge ICO gate 502 - Niederkorn (Collé)   
Darius      HPV472          2017-07-12    17    0006624   Zeebrugge ICO gate 502 - Antwerpen 1333   
and so on...

I hope this makes clear what I want for result.
Is this possible in sql-server ? And if yes then how can I do it offcourse.

EDIT:
One option would be to do it in a stored procedure and just loop over the records and fill and return a temp table. But I was hoping for a solution without stored procedure.

EDIT:
I know this should normally be done in presentation layer but in this case it is difficult. It is a form that receives queries to execute from another table, so it has no knowledge of what resultsets will be received. Therefor it will be not possible in this case to do it on the presentation layer, unless someone has an idea how I can do it here in this situation.

回答1:

This is presentation/formatting issue not a data retrieval problem.

However in case you really are stuck with this problem, it can be done...

NOTE: not tested, you may need to use LEAD not LAG

SELECT
    Chauffeur = CASE WHEN  X.prevsecurysatnr =  X.securysatnr THEN '' ELSE   X.Chauffeur END,
    securysatnr = CASE WHEN  X.prevsecurysatnr =  X.securysatnr THEN '' ELSE   X.securysatnr END,
     X.Datum,
     X.LaadUur,
     X.LotNr,
     X.RitInfo
FROM
    (
    SELECT
            LAG(t.securysatnr) OVER (ORDER BY ISNULL(d.FirstName, '') + ' ' + isnull(d.LastName, ''), convert(date, p.PlanningDate), p.StartTime) AS prevsecurysatnr,
            ISNULL(d.FirstName, '') + ' ' + isnull(d.LastName, '') as Chauffeur,
            t.securysatnr,
            convert(date, p.PlanningDate) as Datum,
            p.StartTime as LaadUur,
            r.LotNr,
            isnull(pFrom.City, '') + ' - ' + isnull(pTo.City, '') as RitInfo 
    from   
        tblPlanning p
        left outer join vwRit r on p.RitID = r.RitID
        left outer join tblPlace pFrom on r.VertrekID = pFrom.PlaceID
        left outer join tblPlace pTo on r.BestemmingID = pTo.PlaceID
        inner join tblDriver d on p.DriverID = d.DriverID
        inner join tblTruck t on p.TruckID = t.TruckID
    where  convert(date, p.PlanningDate) = convert(date, getdate())
    ) X
order by 
    X.Chauffeur,  X.Datum,  X.LaadUur

回答2:

Example

Select Chauffeur   = case when Chauffeur=lag(Chauffeur,1)     over (partition by Chauffeur               Order by RN) then '' else Chauffeur end
      ,securysatnr = case when securysatnr=lag(securysatnr,1) over (partition by Chauffeur,securysatnr Order by RN) then '' else securysatnr end
      ,Datum
      ,LaadUur
      ,LotNr
      ,RitInfo   
 from (
        Select *,RN=Row_Number() over (Order By Chauffeur,securysatnr,Datum)
         From (
                -- Your Complicated Query Goes Here
              ) A1
      ) A
 Order By RN

Returns