sql - How to dynamically calculate the sums of many columns in a GROUP? -
in table below, have variable number of columns, , number in 1000s. need sum values of each of 1000 columns grouped person's name. so, smith's
total test_score_1
, total test_score_2
,...total test_score_1000
. , jackson's
total test_score_1
, total test_score_2
,...total test_score_1000
.
i don't know number of 'test_score_n
' columns beforehand , changing.
so given table:
name test_score_1 test_score_2 ... test_score_1000 smith 2 1 0 jackson 0 3 1 jackson 1 1 2 jackson 3 0 3 smith 4 5 1
how can produce table below?
name test_score_1 test_score_2 ... test_score_1000 smith 6 6 1 jackson 4 4 6
sql generate sql
declare @generatedsql nvarchar(max); set @generatedsql = ( select 'select ' + substring(x.foo, 2, 2000) + 'from ' + quotename(schema_name(t.schema_id)) + '.' + quotename(t.name) + ' group name' --fix line , edited sys.tables t cross apply ( select ', sum(' + quotename(c.name) + ')' sys.columns c c.object_id = t.object_id , c.name <> 'name' xml path('') ) x (foo) t.name = 'mytable' ); exec (@generatedsql);
Comments
Post a Comment