使用TSQL、C#或Python，在不多次命中表的情况下，为所有列计算列的不同记录值？

Question

我在SQL Server中有一个表，它有50列和2亿条记录。目前，我点击该表50次，以对所有列的不同列值进行计数，如下所示：

INSERT INTO dbo.RPTS_LoadValues
(
    Column_Value,
    Record_Value,
    Record_Ct
)
SELECT 'Transaction_Dt',
       CONVERT(VARCHAR(50), Transaction_Dt),
       COUNT_BIG(1)
FROM dbo.VehicleImport
GROUP BY Transaction_Dt
UNION ALL
SELECT 'Purchase_Dt',
       CONVERT(VARCHAR(50), Purchase_Dt),
       COUNT_BIG(1)
FROM dbo.VehicleImport
GROUP BY Purchase_Dt
UNION ALL
SELECT 'PurchaseType_Cd',
       CONVERT(VARCHAR(50), PurchaseType_Cd),
       COUNT_BIG(1)
FROM dbo.VehicleImport
GROUP BY PurchaseType_Cd;

上面的查询运行3列，即交易类型、购买类型和购买类型。还有47列，如汽车类型、车型、车型年份等

使用T-SQL、C#或Python是否还有其他方法可以让我只在表中点击一次，并且能够对所有列使用列的不同记录值？请提供有用的资源或链接

Answer 1

..200万

select colname, colvalue, count(*) as thecounter
from
(
select 
    r.col.value('./@colname[1]', 'nvarchar(128)') as colname,
    r.col.value('./@colvalue[1]', 'nvarchar(max)') as colvalue
from
(
select  
   (
   select
    'object_id' as 'col/@colname', object_id as 'col/@colvalue', '',  column name, column value, ''
    'name' as 'col/@colname', name as 'col/@colvalue', '',
    'column_id' as 'col/@colname', column_id as 'col/@colvalue', '',
    'system_type_id' as 'col/@colname', column_id as 'col/@colvalue', '',
    'user_type_id' as 'col/@colname', user_type_id as 'col/@colvalue', '',
    'collation_name' as 'col/@colname', collation_name as 'col/@colvalue', '',
    'is_sparse' as 'col/@colname', is_sparse as 'col/@colvalue', '',
    'is_column_set' as 'col/@colname', is_column_set as 'col/@colvalue'
   for xml path(''), type
   ) as thexml
from sys.all_columns as t  table goes here
) as src
cross apply src.thexml.nodes('./col') as r(col)
) as d
group by d.colname, d.colvalue;

 verify counters
select *  thecounter=3, 3 rows
from sys.all_columns
where name = 'NodeName'