By Riki Dev


2019-09-11 09:08:54 8 Comments

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

4 comments

@Satish Kumar A 2019-09-11 10:50:58

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

@Shanteshwar Inde 2019-09-11 10:56:29

Can you please add explanation how this answer is works! Thanks!

@Satish Kumar A 2019-09-12 05:46:03

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

@Gordon Linoff 2019-09-11 10:45:18

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.

@Ankit Das 2019-09-11 09:17:05

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');

@Rob Streeting 2019-09-11 09:26:52

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?

@Riki Dev 2019-09-11 09:52:29

thanks, everything works correctly

Related Questions

Sponsored Content

37 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2563437 View
  • 1734 Score
  • 29 Answer
  • Tags:   sql duplicates

3 Answered Questions

[SOLVED] Convert INT to VARCHAR SQL

29 Answered Questions

46 Answered Questions

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

Sponsored Content