I have data in my database like this:

I want to have this with the last date (comparing with day, month year)

I want to have the list of code having the same meta group, do you know how to do it with SQL Server?

CodePudding user response：

The code below inputs the 1st table and outputs the 2nd table exactly. The Meta and Date columns had duplicate values, so in the CTE I took the MAX for both fields. Different logic can be applied if needed.

It uses XML Path to merge all rows into one column to create the List_Code column. The Stuff function removes the leading comma (,) delimiter.

CREATE TABLE MetaTable
(
    Code VARCHAR(5),
    Meta VARCHAR(100),
    Meta_ID INT,
    Date DATETIME
)
GO

INSERT INTO MetaTable
VALUES
    ('A', '1,2', '1', '01/01/2022 08:08:08'),
    ('B', '1,2','2', '01/01/2022 02:00:00'),
    ('B', NULL,'2', '01/01/1900 02:00:00'),
    ('C', NULL,'3', '01/01/2022 02:00:00'),
    ('D', '8','8', '01/01/2022 02:00:00'),
    ('E', '5,6,7', '5', '01/01/2022 02:00:00'),
    ('F', '1,2','2', '01/01/2022 02:00:00')

GO
WITH CTE_Meta
AS
(
    SELECT
        Code,
        MAX(Meta) AS 'Meta',
        Meta_ID,
        MAX(Date) AS 'Date'
    FROM MetaTable
    GROUP BY
        Code,
        Meta_ID
)
SELECT 
    T1.Code,
    T1.Meta,
    T1.Meta_ID,
    STUFF
    (
        (
            SELECT ','   Code
            FROM CTE_Meta T2
            WHERE ISNULL(T1.Meta, '') = ISNULL(T2.Meta, '')
            FOR XML PATH('')
        ), 1, 1, ''
    ) AS 'List_Code',
    T1.Date
FROM CTE_Meta T1
ORDER BY 1

CodePudding user response：

I like the first answer using XML. It's very concise. This is more verbose, but might be more flexible if the data can have different meta values spread about in different records. The CAST to varchar(12) in various places is just for the display. I use STRING_AGG and STRING_SPLIT instead of XML.

WITH TestData as (
    SELECT t.*
    FROM (
        Values
            ('A', '1,2', '1', '01/01/2022 08:08:08'),
            ('B', '1,2', '2', '01/01/2022 02:00:00'),
            ('B', null, '2', '01/01/1900 02:00:00'),
            ('C', null, '3', '01/01/2022 02:00:00'),
            ('D', '8', '8', '01/01/2022 02:00:00'),
            ('E', '5,6,7', '5', '01/01/2022 02:00:00'),
            ('F', '1,2', '2', '01/01/2022 02:00:00'),
            ('G', '16', '17', '01/01/2022 02:00:00'),
            ('G', null, '17', '01/02/2022 03:00:00'),
            ('G', '19', '18', '01/03/2022 04:00:00'),
            ('G', '19', '18', '01/03/2022 04:00:00'),
            ('G', '20', '19', '01/04/2022 05:00:00'),
            ('G', '20', '20', '01/05/2022 06:00:00')
        ) t (Code, meta, meta_ID, date)
), CodeLookup as ( -- used to find the Code from the meta_ID
    SELECT DISTINCT meta_ID, Code
    FROM TestData
), Normalized as ( -- split out the meta values, one per row
    SELECT t.Code, s.Value as [meta], meta_ID, [date]
    FROM TestData t
    OUTER APPLY STRING_SPLIT(t.meta, ',') s
), MetaLookup as ( -- used to find the distinct list of meta values for a Code
    SELECT n.Code, CAST(STRING_AGG(n.meta, ',') WITHIN GROUP ( ORDER BY n.meta ASC ) as varchar(12)) as [meta]
    FROM (
        SELECT DISTINCT Code, meta
        FROM Normalized
        WHERE meta is not NULL
        ) n
    GROUP BY n.Code
), MetaIdLookup as ( -- used to find the distinct list of meta_ID values for a Code
    SELECT n.Code, CAST(STRING_AGG(n.meta_ID, ',') WITHIN GROUP ( ORDER BY n.meta_ID ASC ) as varchar(12)) as [meta_ID]
    FROM (
        SELECT DISTINCT Code, meta_ID
        FROM Normalized
        ) n
    GROUP BY n.Code
), ListCodeLookup as ( -- for every code, get all codes for the meta values
    SELECT l.Code, CAST(STRING_AGG(l.lookupCode, ',') WITHIN GROUP ( ORDER BY l.lookupCode ASC ) as varchar(12)) as [list_Code]
    FROM (
        SELECT DISTINCT n.Code, c.Code as [lookupCode]
        FROM Normalized n
        INNER JOIN CodeLookup c
        ON c.meta_ID = n.meta
        UNION -- every record needs it's own code in the list_code?
        SELECT DISTINCT n.Code, n.Code as [lookupCode]
        FROM Normalized n
    ) l
    GROUP BY l.Code
) 
SELECT t.Code, m.meta, mi.meta_ID, lc.list_Code, t.[date]
FROM (
    SELECT Code, MAX([date]) as [date]
    FROM TestData 
    GROUP BY Code
    ) t
LEFT JOIN MetaLookup m
ON m.Code = t.Code
LEFT JOIN MetaIdLookup mi
ON mi.Code = t.Code
LEFT JOIN ListCodeLookup lc
ON lc.Code = t.Code

Code meta         meta_ID      list_Code    date
---- ------------ ------------ ------------ -------------------
A    1,2          1            A,B,F        01/01/2022 08:08:08
B    1,2          2            A,B,F        01/01/2022 02:00:00
C    NULL         3            C            01/01/2022 02:00:00
D    8            8            D            01/01/2022 02:00:00
E    5,6,7        5            E            01/01/2022 02:00:00
F    1,2          2            A,B,F        01/01/2022 02:00:00
G    16,19,20     17,18,19,20  G            01/05/2022 06:00:00