-- Concatenate with separator (NULL-safe)
SELECT CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName

-- Split string to rows with ordinal
SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,cherry', ',', 1)

-- Aggregate strings with ordering
SELECT DeptID,
       STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY HireDate)
FROM Employees
GROUP BY DeptID

-- Truncate to first of month
SELECT DATETRUNC(month, OrderDate) AS MonthStart

-- Group by week buckets
SELECT DATE_BUCKET(week, 1, OrderDate) AS WeekBucket,
       COUNT(*) AS OrderCount
FROM Orders
GROUP BY DATE_BUCKET(week, 1, OrderDate)

-- Generate date series
SELECT CAST(value AS date) AS Date
FROM GENERATE_SERIES(
    CAST('2024-01-01' AS date),
    CAST('2024-12-31' AS date),
    1
)

-- Running total with partitioning
SELECT OrderID, CustomerID, Amount,
       SUM(Amount) OVER (
           PARTITION BY CustomerID
           ORDER BY OrderDate
           ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal
FROM Orders

-- Get previous non-NULL value (SQL 2022+)
SELECT Date, Value,
       LAST_VALUE(Value) IGNORE NULLS OVER (
           ORDER BY Date
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) AS PreviousNonNull
FROM Measurements

-- Extract scalar value
SELECT JSON_VALUE(JsonColumn, '$.customer.name') AS CustomerName

-- Parse JSON array to rows
SELECT j.ProductID, j.Quantity
FROM Orders
CROSS APPLY OPENJSON(OrderDetails)
WITH (
    ProductID INT '$.productId',
    Quantity INT '$.qty'
) AS j

-- Build JSON object (SQL 2022+)
SELECT JSON_OBJECT('id': CustomerID, 'name': CustomerName) AS CustomerJson
FROM Customers