T-SQL Grouping rows from MAX columns in different rows (?)

Question

I am trying to find a way to combine rows in a table based on the longest row in any of the rows based on row row

Example

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 lines, I would look for the result:

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

EDIT : the above results should be in a table, not just a comma separated by a comma

as you can see as a result, the output should be a long text column in the "akey" field.

I am trying to find a solution that does not include many subqueries in each column, the actual table has 32 columns and more than 13 million rows.

the reason I'm doing this is to create a cleaned table that has the best results in each column for only one identifier per row

, , , , !

.

Answer 1

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 (akey, textn) text *.

: , , , UNPIVOT :

;
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)

Answer 2

, ( SQL2K) :

select test1.akey, A.text1, B.text2, C.text3
from test1
inner join test1 A on A.akey = test1.akey 
inner join test1 B on B.akey = test1.akey 
inner join test1 C on C.akey = test1.akey 
group by test1.akey, A.text1, B.text2, C.text3
having len(a.text1) = max(len(test1.text1))
   and len(B.text2) = max(len(test1.text2))
   and len(C.text3) = max(len(test1.text3))
order by test1.akey

, , , 32 x 13 . :