Formatting of sql server queries

Question

by executing the sql code below I get this result, a row is made for each day, what I want to get instead is the following result:

Desired result sql code:

11/12/2018,7:58,12:46,14:4
10/12/2018,7:57,12:21,13:33,18:8

how do I change the query to get the desired result?

Current result of sql Code:

11/12/2018,7:58
11/12/2018,12:46
11/12/2018,14:4
11/10/2018,7:57
11/10/2018,12:21
11/10/2018,13:33
11/10/2018,18:8

Sql Code:

select FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') as Data,CONCAT(DATEPART(HOUR, DataCreazione), ':', DATEPART(MINUTE, 
DataCreazione)) as Ore from Marcatura where IdUtente = '3' 
and(CONVERT(VARCHAR(10), DataCreazione, 103) between '11-12-2018' and '11-07-2019' )order by FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') desc

Do some googling on SQL Server STUFF. You will get it interesting for sure :) Also you can use GROUP_CONCAT if you are using MSSQL 2017 or newer. — mkRabbani, Sep 11 '19 at 09:15

Ankit Das · Answer 1 · 2019-09-11T09:31:27.193

try this and let me know:

     SELECT FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') as Data,  
            abc = STUFF((SELECT ',' + CONCAT(DATEPART(HOUR, DataCreazione), ':', DATEPART(MINUTE,DataCreazione)) as Ore 
                  from Marcatura
                  FOR XML PATH (''))
                 , 1, 1, '') from Marcatura where IdUtente = '3' 
    and(CONVERT(VARCHAR(10), DataCreazione, 103) between '11-12-2018' and '11-07-2019' )
    group by FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT');

score 0 · Accepted Answer · answered Sep 11 '19 at 09:26

From SQL Server 2017, the STRING_AGG function will do what you need:

select Data, string_agg(Ore, ',') as Ores from (
    -- Your query in here
    select FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') as Data,CONCAT(DATEPART(HOUR, 
    DataCreazione), ':', DATEPART(MINUTE, 
    DataCreazione)) as Ore from Marcatura where IdUtente = '3' 
    and(CONVERT(VARCHAR(10), DataCreazione, 103) between '11-12-2018' and '11-07-2019' )
    order by FORMAT(DataCreazione, 'dd/MM/yyyy', 'it-IT') desc
) t
group by Data

Just to note, this will yield two fields - one with the date and one with a comma delimited list of "Ore"s. You can't really have a query that has a variable number of fields for each row.

If you are in a earlier version of SQL Server, take a look at the answers here: Simulating group_concat MySQL function in Microsoft SQL Server 2005?

score 0 · Answer 3 · answered Sep 11 '19 at 10:45

The answer to your question is string_agg(). However, you should phrase the query as:

select format(convert(date, DataCreazione), 'dd/MM/yyyy', 'it-IT') as Data,
       string_agg(datepart(hour, DataCreazione), ':', datepart(minute, DataCreazione) within group (order by min(DataCreazione) as Ore
from Marcatura
where IdUtente = 3 and
      DataCreazione >= '2018-12-11' and
      DataCreazione < '2019-07-12'
group by convert(date, DataCreazione)
order by DataCreazione desc;

Notes:

IdUtente looks like a number. If so, use a number value for a comparison.
Do not convert dates to strings. SQL Server has a plethora of date/time functionality, so there is no need to change to strings except for output purposes.
Do not use between on date/time values. >= and < work on both dates and times.
Use standard date formats. In SQL Server that is either YYYY-MM-DD or YYYYMMDD.
Use WITHIN GROUP if you want the times in order.

score 0 · Answer 4 · edited Sep 12 '19 at 05:54

0

SELECT CONCAT(DATTE,STRING_AGG(timee,' ,'))
FROM 
(
SELECT 
    FORMAT(CreatedOn,'yyyy-MM-dd') as datte,FORMAT(CreatedOn,' mm:ss') timee
FROM ur_table WITH(NOLOCK)
--WHERE  DATEPART(YY,CreatedOn) =2016
) t
GROUP BY DATTE

edited Sep 12 '19 at 05:54

Ankit Das

640
4
16

answered Sep 11 '19 at 10:50

Satish Kumar A

1
2

Can you please add explanation how this answer is works! Thanks! – Shanteshwar Inde Sep 11 '19 at 10:56
it will get first all dates and their times with separate columns then after grouping by date we are using string_agg for time to get all columns values for a particular date to comma seperated values and displaying them – Satish Kumar A Sep 12 '19 at 05:46

Formatting of sql server queries

4 Answers4

Linked