select row mit maximalen Spaltendaten

Ich habe mehr als 1 Zeilen in einer Tabelle, die zur gleichen ID gehören.

Ich möchte die Zeile auswählen, in der mehr Spalten data enthalten.

z.B.

Meine data sehen so aus

select * from my_table where id=1 PK Id Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 101 1 NULL NULL NULL XX NULL NULL NULL NULL NULL NULL 102 1 NULL NULL NULL XX YY NULL ZZ NULL NULL NULL 103 1 NULL AA NULL NULL NULL NULL NULL NULL NULL NULL 104 1 NULL NULL NULL NULL NULL BB NULL NULL NULL NULL 105 1 NULL NULL NULL NULL NULL NULL NULL CC NULL NULL 

Ich möchte Abfrage, die mir geben wird Ausgabe wie folgt für id = 1: –

 PK Id Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 102 1 NULL NULL NULL XX YY NULL ZZ NULL NULL NULL 

Wo pk ist dein primärer Schlüssel …

 select * from YourBadlyStructuredTable where pk in ( select top 1 pk from YourBadlyStructuredTable unpivot (col for z in (col1,col2,col3, ...))u where id=1 group by pk order by COUNT(*) desc ) 

Wenn Sie Ihre Spaltennamen nicht angeben können …

 select top 1 HorribleTable.* from ( select xmldata.value('(/x2/@PK)[1]','int') as PK1, xmldata.value('(/x2/@Id)[1]','int') as ID1, xmldata.value('count(/x2/@*)','int') as cnt from ( select txquery('.') as xmldata from ( select convert(xml,(select * from HorribleTable for xml raw('x2'))) as x1) v cross apply v.x1.nodes('/x2') t(x) ) v ) v inner join HorribleTable on v.PK1 = HorribleTable.pk where ID1 = 1 order by cnt desc 

Versuche dies:

 with cte as (select *,case when col1 is not null then 1 else 0 end+ case when col2 is not null then 1 else 0 end+ case when col3 is not null then 1 else 0 end+ case when col4 is not null then 1 else 0 end+ case when col5 is not null then 1 else 0 end+ case when col6 is not null then 1 else 0 end+ case when col7 is not null then 1 else 0 end+ case when col8 is not null then 1 else 0 end+ case when col9 is not null then 1 else 0 end+ case when col10 is not null then 1 else 0 end as col_count from my_table where id=1) select * from cte where col_count =(select MAX(col_count) from cte) 
 ;WITH CTE as ( select pk,isnull(col1,'')+isnull(col2,'')+isnull(col3,'')+isnull(col4,'')+isnull(col5,'')+ isnull(col6,'')+isnull(col7,'')+isnull(col8,'')+isnull(col9,'')+isnull(col10,'') as res from tbl4 where id=1) select * from tbl4 where pk in(select pk from CTE where len(res)=(select max(len(res)) from CTE))