基于记录获取列

我理解,由于某种原因,您的数据是非标准化的,如果您需要逻辑来获得结果,您可以使用以下代码:

DECLARE @table TABLE (A varchar(3), B varchar(3),C varchar(3), D varchar(3), E varchar(3))

;WITH cte 
     AS (SELECT ColumnName 
                ,ColumnValue
                ,Row_number() 
                   OVER( 
                     partition BY ColumnName 
                     ORDER BY Rn) ResultOrder 
         FROM   (SELECT Row_number() OVER (ORDER BY (SELECT NULL))Rn,a ,b ,c ,d ,e FROM   @table) p 
            UNPIVOT ( ColumnValue 
                    FOR ColumnName IN( a ,b ,c ,d ,e)) AS unpvt), 
     cte1 
     AS (SELECT Ntile(5) 
                  OVER( 
                    ORDER BY ColumnName) ColumnId 
                ,* 
         FROM   cte), 
     cte2 
     AS (SELECT DISTINCT Isnull(NULLIF(ColumnId - 1, 0), 5) ColumnId 
                         ,ColumnName 
         FROM   cte1 
         WHERE  ColumnValue = 'F') 
SELECT result 
FROM   (SELECT CASE ColumnId 
                 WHEN 1 THEN 'A' 
                 WHEN 2 THEN 'B' 
                 WHEN 3 THEN 'C' 
                 WHEN 4 THEN 'D' 
                 WHEN 5 THEN 'E' 
               END Result 
               ,0  ResultOrder 
        FROM   cte2 
        UNION 
        SELECT ColumnValue 
               ,resultorder 
        FROM   cte1 a 
        WHERE  EXISTS (SELECT 1 
                       FROM   cte2 b 
                       WHERE  a.ColumnId = b.ColumnId)) AS R 
ORDER  BY resultorder 

insert @table VALUES
('A','A','A','A','F')
,('12','32','43','23','2')

A    B    C    D    E
---- ---- ---- ---- ----
A    A    A    A    F
12   32   43   23   2

result
------
D
23
A

insert @table VALUES
('F','A','A','A','A')
,('12','32','43','23','2')
A    B    C    D    E
---- ---- ---- ---- ----
F    A    A    A    A
12   32   43   23   2

result
------
E
A
2

说明:

• 热膨胀系数
• 表基于列名创建一个组,使用NTIL创建5个组
• CTE2
• 选择

您可以使用

SELECT ColumnValue Result              
FROM   cte1 a 
WHERE  EXISTS (SELECT 1 
FROM   cte2 b 
WHERE  a.ColumnId = b.ColumnId)

Result
------
A
23

(2 row(s) affected)

SELECT ColumnValue Result              
FROM   cte1 a 
WHERE  EXISTS (SELECT 1 
FROM   cte2 b 
WHERE  a.ColumnId = b.ColumnId) 
AND a.ResultOrder = 2

Result
------
23

(1 row(s) affected)