Add two values of a column where the date is the same?

Question

I have formed a view from several tables. This view contains, for example, the columns Order Number, Name and Datetime.

FOR EXAMPLE

Original:

| Order number  | Name      | Datetime                  |
|-------------: |--------   |-------------------------  |
|        -1094  | Bob       | 2019-04-02 12:58:56.000   |
|        -1989  | Anna      | 2018-03-27 09:13:53.000   |
|          -43  | Peter     | 2018-04-16 10:20:40.000   |
|        -1094  | Dieter    | 2017-12-30 11:28:23.000   |
|        -1094  | Sabi      | 2019-04-02 12:58:56.000   |

I have written a function which adds the names and returns them to me. The problem is that the query takes a lot of time to display the results. For about 1000 records it takes about 1 hour. This is much too slow. I hope you can give me some tips on how to improve the function or a completely different solution.

USE [****]
GO
/****** Object:  UserDefinedFunction [dbo].[*_***_sfGetProjectName]    Script Date: 19.06.2019 14:03:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[*_***_sfGetProjectName](@starttime datetime) RETURNS VARCHAR(MAX)
BEGIN
    DECLARE tmpCursor CURSOR FOR SELECT Projektname FROM dbo.*_***_Sollzeit_Istzeit2 WHERE Startzeit = @starttime
    DECLARE @tmpProjectName VARCHAR(MAX)
    DECLARE @ReturnProjectName VARCHAR(MAX)
    DECLARE @tmpCursorRows INTEGER

    SET @tmpProjectName = ''
    SET @ReturnProjectName = ''
    SET @tmpCursorRows = 0

    OPEN tmpCursor
    SET @tmpCursorRows = @@CURSOR_ROWS
    FETCH NEXT FROM  tmpCursor INTO @tmpProjectName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @tmpCursorRows > 1 
        BEGIN
            SET @ReturnProjectName = @ReturnProjectName + '_' + @tmpProjectName
        END
        ELSE
        BEGIN
            SET @ReturnProjectName = @tmpProjectName
        END

        FETCH NEXT FROM tmpCursor INTO @tmpProjectName
    END

    CLOSE tmpCursor
    DEALLOCATE tmpCursor

    RETURN @ReturnProjectName
END;

I want to add all names where the value of the column Datetime is equal.

Result:

| Order number  | Name      | Datetime                  |
|-------------: |---------- |-------------------------  |
|        -1094  | Bob_Sabi  | 2019-04-02 12:58:56.000   |
|        -1989  | Anna      | 2018-03-27 09:13:53.000   |
|          -43  | Peter     | 2018-04-16 10:20:40.000   |
|        -1094  | Dieter    | 2017-12-30 11:28:23.000   |

Possible duplicate of [get a comma delimited string from rows](https://stackoverflow.com/questions/41787834/get-a-comma-delimited-string-from-rows) — Zohar Peled, Jun 19 '19 at 12:23
Possible duplicate of [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) — Eric Brandt, Jun 19 '19 at 13:20

score 0 · Accepted Answer · answered Jun 19 '19 at 12:28

If you are working with SQL Server 2017 or higher, use string_agg. For lower versions, use a combination of stuff and for xml:

For SQL Server 2016 or lower:

SELECT DISTINCT [Order number], 
       (
           STUFF(
           (
               SELECT '_' + Name
               FROM <YourViewNameHere> As T1
               WHERE T1.[Datetime] = T0.[Datetime]
               -- Unremark if you want the names to be sorted in the output
               -- ORDER BY Name 
               FOR XML PATH('')
           ), 1, 1, '')
       ) As Name      ,
       [Datetime]
FROM <YourViewNameHere> As T0

For Sql Server 2017 or higher:

SELECT [Order number], STRING_AGG(Name, '_') As Name, [Datetime]
FROM <YourViewNameHere> 
GROUP BY [Order number], [Datetime]

Add two values of a column where the date is the same?

1 Answers1