-
count DATA in ALL columns
Hello,
I am pretty new in SQL an have a todo for work. Hopefully you understand my problem as I am not that fluent in english. Anyway:
What I got:
1 table
lots of columns
What I need:
the sum of all different values in all columns
What I found out:
SELECT
COUNT (DISTINCT SATZ_ID),
COUNT (DISTINCT KONSTANTE_5)
COUNT (...)
...
FROM INT_MA1AKI_MIG
## This is pretty much what I need, this shows all the number of different values in those columns BUT I don't want to write COUNT... for all of the columns, that would be too much
QUESTION:
Is it possible to print out the above automatically without typing so much?
Tanks
-
-
declare @query varchar(8000)
declare @tablename varchar(128)
declare @col varchar(128)
declare @count1 int
declare @maxcount int
--ADD YOUR TABLENAME HERE
set @tablename ='customersummary'
set @count1=1
set @maxcount =(select count(*) from syscolumns where object_name(id)=@Tablename)
set @query = 'Select '
--print @maxcount
while @count1 <=@maxcount
begin
set @col=(select name from syscolumns where object_name(id)=@Tablename and colorder =@count1)
if @count1<@maxcount
begin
set @query= @query +' count('+@col+'),'
end
if @count1=@maxcount
begin
set @query= @query +' count('+@col+')'
end
set @count1=@count1+1
end
set @query = @query+' from '+@tablename
print @query
-
--with column alias
declare @query varchar(8000)
declare @tablename varchar(128)
declare @col varchar(128)
declare @count1 int
declare @maxcount int
--Change it to your table name
set @tablename ='customersummary'
set @count1=1
set @maxcount =(select count(*) from syscolumns where object_name(id)=@Tablename)
set @query = 'Select '
--print @maxcount
while @count1 <=@maxcount
begin
set @col=(select name from syscolumns where object_name(id)=@Tablename and colorder =@count1)
if @count1<@maxcount
begin
set @query= @query +' count('+@col+') as '+@col+','
end
if @count1=@maxcount
begin
set @query= @query +' count('+@col+') as '+@col
end
set @count1=@count1+1
end
set @query = @query+' from '+@tablename
print @query
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|