query catat nilai max dari setiap kolom seri

SELECT TOP 1000 [id]
      ,[Seri1]
      ,[Seri2]
      ,[Seri3]
  FROM [TES].[dbo].[SeriesData]
------------------------
  GO

 SELECT t.[id],
  (
SELECT
MAX(SeriN)
FROM
(
VALUES (t.[Seri1]),(t.[Seri2]),(t.[Seri3])
) AS Series(SeriN)
  ) AS [Series]
  FROM [dbo].[SeriesData] AS t
-------------------------
  GO 
 SELECT t.[id],
  (
SELECT
MAX(SeriN)
FROM
(
SELECT t.[Seri1] AS [SeriN] UNION ALL
SELECT t.[Seri2] AS [SeriN] UNION ALL
SELECT t.[Seri3] AS [SeriN]


) AS m
  ) AS [Series]
  FROM [dbo].[SeriesData] AS t
  -------------------------
  GO 
SELECT id,
MAX([SeriN]) as [Series]
  FROM [dbo].[SeriesData]
UNPIVOT
(
[SeriN]
FOR [Seri]
IN([Seri1],[Seri2],[Seri3])
) as pvt
GROUP BY
[id]

------------------------------
GO
SELECT
t.[id],
MAX(t.[SeriN]) AS [SERIES]
FROM
(
SELECT
s1.[id],
s1.Seri1 as SeriN
FROM [dbo].[SeriesData] AS s1
UNION ALL
SELECT
s2.[id],
s2.Seri2 as SeriN
FROM [dbo].[SeriesData] AS s2
UNION ALL
SELECT
s3.[id],
s3.Seri3 as SeriN
FROM [dbo].[SeriesData] AS s3
) AS t
group by t.[id]
=------------------
go
;with m as(
SELECT
t.[id],
MAX(CASE n.[rn]
when 1 then t.[Seri1]
when 2 then t.[Seri2]
else t.[Seri3]
end
) AS [Series]
From
[dbo].[SeriesData] as t
CROSS JOIN
(
SELECT 1 AS [rn] UNION ALL
SELECT 2 AS [rn] UNION ALL
SELECT 3 AS [rn]
) AS n
GROUP BY t.[id]
)
SELECT
t.[id],
m.[Series]
From
[dbo].[SeriesData] as t
inner join m
on m.id = t.id
-----------------------------
USE [TES]

USE [TES]

SELECT KD_KANTOR, NO_DAFTAR,
 ROW_NUMBER ( ) OVER (
        PARTITION BY KD_KANTOR,NO_DAFTAR ORDER BY
        KD_KANTOR ASC
        , NO_DAFTAR ASC
       
        ) AS SEQ
FROM UT20_BEA_GAB_TED_PEB_FTZ
order by KD_KANTOR, SEQ ASC

Tweet