Help needed with SQL

Transact-SQL
1

Hello,

I have following simplified table structure

CREATE TABLE [TERMINAL_SAYIM](
	[LaptopId] [bigint] IDENTITY(1,1) NOT NULL primary key,
	[AdresKodu] [nvarchar](30) NULL,
	[BelgeNo] [nvarchar](30) NOT NULL,
	[IslemTuru] [nvarchar](2) NULL,
	[Barkod] [nvarchar](30) NOT NULL,
	[SSCC] [nvarchar](30) NULL,
	[OkutmaTarihSaati] [datetime] NOT NULL,
    [KullaniciKodu] [nvarchar](30) NULL
)

This table holds information about warehouse counting. Normally, one SSCC has to be counted once. It has to be checked for correctness if same SSCC is counted by two different users. I would like to have a list where I can see double reading of same SSCC. I came up with a query as following, but that is not exactly what I want.

select sscc, KullaniciKodu
from TERMINAL_SAYIM
where 
  BelgeNo = '2017-12' and
  IslemTuru = 'SA' and
  AdresKodu = 'SAYIM'
group by sscc, KullaniciKodu
order by sscc, kullaniciKodu

Resulting following output

sscc                           KullaniciKodu
------------------------------ ------------------------------
(DEFAULT)                      K01
(DEFAULT)                      K02
(DEFAULT)                      K07
X                              K02
Y                              K03
Y                              K05
Y                              K06

(7 row(s) affected)

Above result tells me that I have problem with (DEFAULT) and Y. However, I would like a single line for each SSCC and number indicating different user counting next to it. If possible only numbers bigger than 1 would list me the final report of short list.

Any help is appreciated.

Edit: It is my mistake that I did not include resulting table. What I want as a result is below

sscc                           total
------------------------------ ------------------------------
(DEFAULT)                      3
Y                              3

(2 row(s) affected)
2 
select sscc, KullaniciKodu1, KullaniciKodu2, KullaniciKodu3, KullaniciKodu4, KullaniciKodu5, KullaniciKodu6
from (
    select sscc,
        MAX(case when ROW_NUMBER = 1 then KullaniciKodu end) as KullaniciKodu1,
        MAX(case when ROW_NUMBER = 2 then KullaniciKodu end) as KullaniciKodu2,
        MAX(case when ROW_NUMBER = 3 then KullaniciKodu end) as KullaniciKodu3,
        MAX(case when ROW_NUMBER = 4 then KullaniciKodu end) as KullaniciKodu4,
        MAX(case when ROW_NUMBER = 5 then KullaniciKodu end) as KullaniciKodu5,
        MAX(case when ROW_NUMBER = 6 then KullaniciKodu end) as KullaniciKodu6
    from (
        select *, ROW_NUMBER() over(partition by sscc order by KullaniciKodu) as row_num
        from TERMINAL_SAYIM
        where 
          BelgeNo = '2017-12' and
          IslemTuru = 'SA' and
          AdresKodu = 'SAYIM'
    ) as derived
    group by sscc
) as derived2
where KullaniciKodu2 > ''
3

Hello ScottPletcher,
Thank you for your time.
When I try copy paste on my sql2012 I get error "Invalid column name ROW_NUMBER" on line 4.
If I convert to ROW_NUMBER() then I get another error "function ROW_NUMBER must have an OVER clause"

EDIT: I just added my requested result table in my initial post. Sorry that I didn't do it in the first place.

4

I think I could find a solution after seeing ScottPletcher's reply. Below is the SQL I came up with. Please feel free to enhance it as I do not know if this is a good SQL in terms of performance.

select sscc, count(sscc) toplam
from (
  select sscc, KullaniciKodu
  from TERMINAL_SAYIM
  where   
    BelgeNo = '2017-12' and
    IslemTuru = 'SA' and
    AdresKodu = 'SAYIM'
  group by sscc, KullaniciKodu
) resultset
group by sscc
having count(sscc) > 1

Result is:

sscc                           toplam
------------------------------ -----------
(DEFAULT)                      3
Y                              3

(2 row(s) affected)