T-SQL 将来自不同行中的 MAX 长度列的行分组(?) [英] T-SQL Grouping rows from the MAX length columns in different rows (?)

问题描述

我试图想出一种方法，根据行键中任何行中最长的字符串组合表中的行

i'm trying to come up with a way to combine rows in a table based on the longest string in any of the rows based on a row key

示例

CREATE TABLE test1 
    (akey int not null , 
    text1 varchar(50) NULL, 
    text2 varchar(50) NULL, 
    text3 varchar(50) NULL  )


INSERT INTO test1 VALUES ( 1,'Winchester Road','crawley',NULL)
INSERT INTO test1 VALUES ( 1,'Winchester Rd','crawley','P21869')
INSERT INTO test1 VALUES ( 1,'Winchester Road','crawley estate','P21869')
INSERT INTO test1 VALUES ( 1,'Winchester Rd','crawley','P21869A')
INSERT INTO test1 VALUES ( 2,'','birmingham','P53342B')
INSERT INTO test1 VALUES ( 2,'Smith Close','birmingham North East','P53342')
INSERT INTO test1 VALUES ( 2,'Smith Cl.',NULL,'P53342B')
INSERT INTO test1 VALUES ( 2,'Smith Close','birmingham North','P53342')

对于这些行，我将寻找以下结果:

with these rows i would be looking for the result of :

1   Winchester Road,    crawley estate, P21869A
2   Smith Close,    birmingham North East,  P53342B

EDIT:上面的结果需要在表格中，而不仅仅是逗号分隔的字符串

EDIT: the results above need to be in a table rather than just a comma separated string

正如您在结果中看到的，输出应该是 'akey' 字段范围内最长的文本列.

as you can see in the result, the output should be the longest text column in the range of the 'akey' field.

我正在尝试提出一个解决方案，该解决方案不涉及每列上的大量子查询，实际表有 32 列和超过 1300 万行.

i'm trying to come up with a solution that does not involve lots of subqueries on each column, the actual table has 32 columns and over 13 million rows.

我这样做的原因是创建一个清理表，每行只有一个 ID，在每一列中都有最好的结果

the reason i'm doing this is to create a cleaned-up table that has the best results in each column for just one ID per row

这是我的第一篇文章，所以如果您需要更多信息，请告诉我，我很高兴听到有关我已破坏的发布的任何最佳做法！

this is my first post, so let me know if you need any more info, and i'm happy to hear about any best practices about posting that i've broken!

谢谢

本.

推荐答案

SELECT A.akey, 
    (
        SELECT TOP 1 T1.text1
        FROM test1 T1
        WHERE T1.akey=A.akey AND LEN(T1.TEXT1) = MAX(LEN(A.text1))
    ) AS TEXT1,
    (
        SELECT TOP 1 T2.text2
        FROM test1 T2
        WHERE T2.akey=A.akey AND LEN(T2.TEXT2) = MAX(LEN(A.text2))
    ) AS TEXT2,
    (
        SELECT TOP 1 T3.text3
        FROM test1 T3
        WHERE T3.akey=A.akey AND LEN(T3.TEXT3) = MAX(LEN(A.text3))
    ) AS TEXT3
FROM TEST1 AS A
GROUP BY A.akey

我刚刚意识到你说你有 32 列.我不认为这样做的好方法，除非 UNPIVOT 允许您为每个 text* 列创建单独的行(akey、textn).

I just realized you said you have 32 columns. I don't see a good way to do that, unless UNPIVOT would allow you to create separate rows (akey, textn) for each text* column.

我今天可能没有机会完成这个，但 UNPIVOT 看起来很有用:

I may not have a chance to finish this today, but UNPIVOT looks useful:

;
WITH COLUMNS AS
(
    SELECT akey, [Column], ColumnValue
    FROM
        (
            SELECT X.Akey, X.Text1, X.Text2, X.Text3
            FROM test1 X
        ) AS p
    UNPIVOT (ColumnValue FOR [Column] IN (Text1, Text2, Text3))
    AS UNPVT
)
SELECT *
FROM COLUMNS
ORDER BY akey,[Column], LEN(ColumnValue)

这篇关于T-SQL 将来自不同行中的 MAX 长度列的行分组(?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！