current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I need to determine the duplicates within a large number of different columns. Rather than typing out each column name, I tried to use the following code. This is slightly more efficient because I only need to type the column name once. However, I get this error:

Msg 164, Level 15, State 1, Line 8 Each GROUP BY expression must contain at least one column that is not an outer reference.

declare @TheCount varchar(100)
set @TheCount = 'Column_1'
select @TheCount, count(@TheCount) as LineCount
from staging
group by @TheCount
having count(@TheCount) > 1
order by 2 desc
share|improve this question
    
What and Why "this Error": –  huMpty duMpty Jul 12 '13 at 16:22
    
You will have to look at using dynamic sql if you want to use variables as the column names. See the curse and blessing of dynamic sql –  bluefeet♦ Jul 12 '13 at 16:22

2 Answers 2

up vote 0 down vote

Maybe this script will be useful (assuming that is Sql Server):

CREATE TABLE TEST(
a int
)

INSERT INTO TEST VALUES(1)
INSERT INTO TEST VALUES(1)
INSERT INTO TEST VALUES(2)
INSERT INTO TEST VALUES(3)
INSERT INTO TEST VALUES(4)
INSERT INTO TEST VALUES(4)

declare @TheCount varchar(100)
set @TheCount = 'a'

EXEC('select ' + @TheCount + ', count(' + @TheCount + ') as LineCount
      from test
      group by ' + @TheCount + '
      having count(' + @TheCount + ') > 1
      order by 2 desc')

You can try this example here.

Note: this is a little example of "Dynamic Sql Execution", here a link for a little explanation.

share|improve this answer
up vote -1 down vote 
DECLARE @column_list varchar(4000) = 'column_name1,column_name2,column_name3,etc';

DECLARE @sql = '
  WITH dups AS (
    SELECT 
      *,ROW_NUMBER() OVER(PARTITION BY '+@column_list'+)) AS n
    FROM table_name
  SELECT * FROM dups WHERE n > 1';

EXEC @sql;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.