How to convert rows to columns and find top string

Question

Need some help to solve below. I have a table:

expected result:

below logic should be applied by SQL. IF "Indicator" = T1, then need to find lowest "KEY-XXX-1", Column "IN/OUT" should be converted into separate Columns and insert the dates. If "Indicator" = T2, then need to find top "KEY-X-?" by last digits after '-', convert "IN/OUT" into separate Columns and take corresponding dates.

any help would be greatly appreciated!

Why the rows (KEY-L-1, T2, 2022-08-09, 2022-08-10) and (KEY-L-2, T1, 2022-08-11, 2022-08-12) are excluded from the expected output? also, is it always *one digit* at the end of the key? — ahmed, Feb 05 '23 at 06:31

score 1 · Accepted Answer · answered Feb 04 '23 at 10:37

Query:

select t.`KEY`, t.Indicator, 
substring_index(t.dates, ',', 1) as `Date IN`,
substring_index(t.dates, ',', -1) as `Date OUT`
from (
 select
  CONCAT(
   rs.first_key, '-',
   rs.middle_key, '-',
   case
    when rs.indicator = 'T1' then MIN(rs.last_key)
    when rs.indicator = 'T2' then MAX(rs.last_key)
    end
  ) as `KEY`,
  rs.indicator as Indicator,
  case 
   when rs.indicator = 'T1' then
    substring_index(group_concat(
     rs.date
     order by rs.last_key, rs.IN_OUT separator ','
    ), ',', 2)
   when rs.indicator = 'T2' then
    substring_index(group_concat(
     rs.date
     order by rs.last_key desc, rs.IN_OUT separator ','
    ), ',', 2)
  end as dates
  from(    
   select 
    substring_index(`Key`, '-', 1) as first_key,
    substring_index(substring_index(`Key`, '-', 2), '-', -1) as middle_key,
    substring_index(substring_index(`Key`, '-', 3), '-', -1) as last_key, 
    indicator,
    IN_OUT,
    date  
    from records
  ) as rs
  group by rs.first_key, rs.middle_key, rs.indicator
) as t;

Output:

Thank you for the answer will check and let you know if it has solved my issue. — Petras, Feb 04 '23 at 11:28

score 1 · Answer 2 · answered Feb 08 '23 at 14:21

I hope I understand your question correctly.

Data Prep

CREATE TABLE entry_log(
    key_code nvarchar(20) NULL,
    indicator nvarchar(3) NULL,
    in_out nvarchar(4) NULL,
    date date NULL
) 

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-T-1', N'T1', N'IN', CAST(N'2022-08-01' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-T-1', N'T1', N'OUT', CAST(N'2022-08-02' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-T-2', N'T1', N'IN', CAST(N'2022-08-03' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-T-2', N'T2', N'OUT', CAST(N'2022-08-04' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-T-3', N'T2', N'IN', CAST(N'2022-08-05' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-T-3', N'T2', N'OUT', CAST(N'2022-08-06' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-1', N'T1', N'IN', CAST(N'2022-08-07' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-1', N'T1', N'OUT', CAST(N'2022-08-08' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-1', N'T2', N'IN', CAST(N'2022-08-09' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-1', N'T2', N'OUT', CAST(N'2022-08-10' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-2', N'T1', N'IN', CAST(N'2022-08-11' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-2', N'T1', N'OUT', CAST(N'2022-08-12' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-2', N'T2', N'IN', CAST(N'2022-08-13' AS Date))

INSERT entry_log (key_code, indicator, in_out, date) VALUES (N'KEY-L-2', N'T2', N'OUT', CAST(N'2022-08-14' AS Date))

Result Query :

WITH rs_t1 AS
(
SELECT 
    *,
    DENSE_RANK() OVER (PARTITION BY SUBSTRING(key_code,1,LEN(key_code)-CHARINDEX('-',REVERSE(key_code)))
                        ORDER BY SUBSTRING(key_code,LEN(key_code)-CHARINDEX('-',REVERSE(key_code))+2,LEN(key_code)) ASC) AS RNK
FROM 
    entry_log
WHERE
    indicator='T1'
),
rs_t2 AS
(
SELECT 
    *,
    DENSE_RANK() OVER (PARTITION BY SUBSTRING(key_code,1,LEN(key_code)-CHARINDEX('-',REVERSE(key_code)))
                        ORDER BY SUBSTRING(key_code,LEN(key_code)-CHARINDEX('-',REVERSE(key_code))+2,LEN(key_code)) DESC) AS RNK
FROM 
    entry_log
WHERE
    indicator='T2'
)
SELECT 
    key_code,
    indicator,
    max([Date IN])  AS  [Date IN],
    max([Date OUT]) AS  [Date OUT]
FROM 
(
    SELECT
        key_code,
        indicator,
        date    AS [Date IN],
        NULL AS [Date OUT]
    FROM
        rs_t1
    WHERE
        in_out='IN'
    AND
        rnk=1
    UNION
    SELECT
        key_code,
        indicator,
        NULL    AS [Date IN],
        date AS [Date OUT]
    FROM
        rs_t1
    WHERE
        in_out='OUT'
    and rnk=1
)t1
GROUP BY
    key_code,
    indicator
UNION
SELECT 
    key_code,
    indicator,
    max([Date IN]) AS   [Date IN],
    max([Date OUT]) AS  [Date OUT]
FROM 
(
    SELECT
        key_code,
        indicator,
        date    AS [Date IN],
        NULL AS [Date OUT]
    FROM
        rs_t2
    WHERE
        in_out='IN'
    AND
        rnk=1
    UNION
    SELECT
        key_code,
        indicator,
        NULL    AS [Date IN],
        date AS [Date OUT]
    FROM
        rs_t2
    WHERE
        in_out='OUT'
    and rnk=1
)t2
GROUP BY
    key_code,
    indicator
ORDER BY [Date IN]

Output

How to convert rows to columns and find top string

2 Answers2