I have a table in SQL in the following format

Is it possible to rank it vertically and have results as below

CodePudding user response：

You can do something like this in MSSQL

DECLARE @colQuery NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @colQuery = '';
SELECT @colQuery = @colQuery   N'CASE WHEN '  A.[Column]   N' IS NOT NULL THEN '''   A.[Name]  ' '' ELSE '''' END, '
FROM (
select
   syscolumns.name as [Column],
   REPLACE(syscolumns.name,'_Date','') as [Name]
from 
   sysobjects 
inner join 
   syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and   sysobjects.name = 'TeraData'
and syscolumns.name like 'P%') A
GROUP BY A.[Column], a.[Name] 
order by A.[Column] desc, a.[Name] desc

SET @query = 'SELECT [ID], CONCAT(' substring(@colQuery, 1, (len(@colQuery) - 1)) ') as Rank from TeraData';
EXECUTE (@query);

CodePudding user response：

With a lateral join you can treat the columns as rows.
Then use group_concat to create a string.

select *
from your_table
cross join lateral (
  select group_concat(P_CODE order by p_date separator ' ') as `Rank`
  from (
    select 'P1' AS P_CODE, P1_DATE AS P_DATE 
    union all select 'P2', P2_DATE
    union all select 'P3', P3_DATE
    union all select 'P4', P4_DATE
  ) q
  where p_date is not null
) p;

ID | P1_Date    | P2_Date    | P3_Date    | P4_Date    | Rank       
-: | :--------- | :--------- | :--------- | :--------- | :----------
 1 | 2021-09-30 | 2021-09-26 | 2021-09-24 | 2021-09-25 | P3 P4 P2 P1
 2 | 2021-08-20 | 2021-08-11 | 2021-07-24 | 2021-06-25 | P4 P3 P2 P1
 3 | 2021-08-20 | null       | 2021-07-24 | 2021-06-25 | P4 P3 P1   

db<>fiddle here

CodePudding user response：

In Teradata you can unpivot, rank & pivot like this:

WITH cte AS
 (
   SELECT id, col, 
      Row_Number()
      Over(PARTITION BY id
           ORDER BY dt) AS rn
   FROM my_table
   UNPIVOT(dt FOR col IN(P1_Date AS 'P1'
                        ,P2_Date AS 'P2'
                        ,P3_Date AS 'P3'
                        ,P4_Date AS 'P4')) AS p
 )
SELECT pvt.*,
   Coalesce(r1, '') ||
   Coalesce(' ' || r2, '') ||
   Coalesce(' ' || r3, '') ||
   Coalesce(' ' || r4, '')   
FROM cte
PIVOT (Max (col) 
       FOR rn IN (1 AS r1
                 ,2 AS r2
                 ,3 AS r3
                 ,4 AS r4)
      ) AS pvt 

CodePudding user response：

Use UNION ALL to normalize the table so that you get 1 row for each ID and aggregate to get the result with GROUP_CONCAT():

WITH cte AS (
  SELECT ID, P1_Date date, 1 n FROM tablename UNION ALL
  SELECT ID, P2_Date, 2 FROM tablename UNION ALL
  SELECT ID, P3_Date, 3 FROM tablename UNION ALL
  SELECT ID, P4_Date, 4 FROM tablename 
)
SELECT ID, GROUP_CONCAT('P', n ORDER BY date SEPARATOR ' ') `Rank`
FROM cte
WHERE date IS NOT NULL
GROUP BY ID;

See the demo.