2

I have a table in Microsoft SQL Server like the following:

Main_Key IDx IDy IDz Count
123450 0 0 0 0
123450 0 0 1 1
123450 0 0 2 0
123450 0 0 3 5
123450 1 0 0 4
123450 1 0 1 3
123450 1 0 2 8
123450 1 0 3 0
123450 0 1 0 5
123450 0 1 1 6
123450 0 1 2 0
123450 0 1 3 0
123450 1 1 0 6
123450 1 1 1 2
123450 1 1 2 3
123450 1 1 3 1
123450 0 2 0 4
123450 0 2 1 5
123450 0 2 2 0
123450 0 2 3 7
123450 1 2 0 9
123450 1 2 1 9
123450 1 2 2 2
123450 1 2 3 5
123451 ... ... ... ...
  • ID1 can be 0 or 1.
  • ID2 can be 0, 1, or 2.
  • ID3 can be 0, 1, 2, or 3.

I want to unpivot this like the following table:

Main_Key ID_Type Count
123450 IDx_0_IDy_0 6
123450 IDx_1_IDy_0 12
123450 IDx_0_IDy_1 11
123450 IDx_1_IDy_1 12
123450 IDx_0_IDy_2 16
123450 IDx_1_IDy_2 25
123450 IDx_0_IDz_0 20

As you can see, what I like to see is that I want to create a cross tab between different values of each ID. In fact, in the ID_Type column, whenever one sees a ID like IDx_a_IDy_b with the Main_Key = ZZZ, the count variable will be the sum of the count variables for all the rows having Main_Key = ZZZ and IDx = a and IDy = b (a 2x2 grid between each two IDs).

I already solved this by first creating the grid by many "CASE" conditions:

select
     main_key,
     sum(case when IDx = 0 and IDy = 0 then count else 0 end) as IDx_0_IDy_0,
     sum(case when IDx = 1 and IDy = 0 then count else 0 end) as IDx_1_IDy_0,
     sum(case when IDx = 0 and IDy = 1 then count else 0 end) as IDx_0_IDy_1,
from
     table
group by
     main_key

and then unpivot this either using the "UNPIVOT" method or "UNION ALL" method. However, this method takes a very long time, especially with the fact that there will be like 15 million records in my table. Is there any built-in method in SQL that can do such a thing? or make this query faster? Much appreciated :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shawn
  • 21
  • 2

3 Answers3

1

It looks like you simply need to group also by IDx and IDy

select
  main_key,
  IDx,
  IDy,
  SUM(count)
FROM YourTable t
GROUP BY
  main_key,
  IDx,
  IDy;

db<>fiddle

For this to perform well, you would need the following index, this will also improve your original query

  INDEX IX CLUSTERED (Main_key, IDx, IDy)
-- alternatively
  INDEX IX NONCLUSTERED (Main_key, IDx, IDy) INCLUDE (count)
Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

first Calculate count Idx_a_IDY_b and Calculate count Idx_a_IDz_b

select 

          Main_Key
         ,'IDx_'+cast(IDx as varchar(100))+'_IDy_' +cast(IDy as varchar(100)) as ID_Type
         ,a.Count
from (
        select     
                      Main_Key
                     ,IDx
                     ,IDy
                     ,sum(count) over(partition by Main_Key,IDx,IDy) as Count
                     ,ROW_NUMBER() over(partition by Main_Key,IDx,IDy order by Main_Key) as rw
        from Table
)a
where a.rw=1

union all
select 
          Main_Key
         ,'IDx_'+cast(IDx as varchar(100))+'_IDz_' +cast(IDz as varchar(100)) as ID_Type
         ,a.Count
from (
            select 
                      Main_Key
                     ,IDx
                     ,IDz
                     ,sum(count) over(partition by Main_Key,IDx,IDz) as Count
                     ,ROW_NUMBER() over(partition by Main_Key,IDx,IDz order by Main_Key) as rw
            from  Table
)a
where a.rw=1
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20
0

I found the exact answer that I was looking for. This request can be done with the following command:

select 
     main_key,
     ID_type,
     sum(count) as count
from 
     table_name
cross apply
     (
     values
          ('IDx_' + cast(IDx as varchar(10)) + '_IDy_' + cast(IDy as varchar(10))),
          ('IDx_' + cast(IDx as varchar(10)) + '_IDz_' + cast(IDz as varchar(10))),
          ('IDz_' + cast(IDz as varchar(10)) + '_IDy_' + cast(IDy as varchar(10)))
     ) as t(ID_type)
group by
     main_key,
     ID_type

And then it creates the exact output table that I mentioned above. All you need to specify is the each possible combination of two IDs that you like to have in your ID_type column.

Shawn
  • 21
  • 2