Group Differently Or Change to Multiple Subqueries

Question

I am a .net developer that doesn't do a lot of SQL other than basic CRUD.

I have a table in SQL Server 2005:

CREATE TABLE [dbo].[Cases](
    [CasesID] [int] IDENTITY(1,1) NOT NULL,
    [Enterprise_Date_Key] [int] NOT NULL,
    [Interval_Num] [int] NOT NULL,
    [QueueID] [int] NOT NULL,
    [TotalCases] [int] NOT NULL,

With data similar to:

4609    3   0   12455   4532
4610    3   0   12452   7963
4625    3   1   12455   4542
4626    3   1   12452   7993
4627    3   2   12455   4552
4628    3   2   12452   7823
.

And I run the query:

set @enterpriseDateKey = 3
select QueueID, interval_num, max(TotalCases)
    from Case_Process_Status_Hourly_Fact a  
    where a.Enterprise_Date_Key = @enterpriseDateKey    
    group by QueueID,Interval_Num

The results are:

12452   0   4532
12455   0   7963
12452   1   4542
12455   1   7993
12452   2   4552
12455   3   7823
.
.
.

I need help with a query that will group the data differently where the Intervals are the columns (X axis) and the QueueID are grouped on the rows (Y axis). Such as:

12452   4532    4542    4552    .   .
12455   7963    7993    7823    .   .

I will be honest, I don't know which direction to go to get the desired results. I don't know if I should go down the path of creating multiple subqueries to get my data or if there is any way to group differently to get my desired results. Any advice would be extremely helpful.

score 1 · Accepted Answer · answered Dec 26 '12 at 16:04

There is more than one way to pivot results from rows to columns in SQL Server. In this case, the most obvious approach seems to be to use aggregation:

select QueueID,
      max(case when interval_num = 0 then TotalCases end) as Int0,
      max(case when interval_num = 1 then TotalCases end) as Int1,
      max(case when interval_num = 2 then TotalCases end) as Int2,
      max(case when interval_num = 3 then TotalCases end) as Int3,
      . . .
from Case_Process_Status_Hourly_Fact a  
where a.Enterprise_Date_Key = @enterpriseDateKey    
group by QueueID

This is essentially your query with the select clause having the pivoted columns. In SQL, you have to specify the number of columns, so put in the number you think necessary.

Thank you soo much! With my inexperience I didn't even see what I was trying to do. You are right, I need to pivot the data and this is an easy way to do that using the case when clause for each column. I appreciate your time. — Patrick, Dec 26 '12 at 16:16

Taryn · Answer 2 · 2012-12-26T17:27:18.630

Since you are using SQL Server 2005 you can also implement the PIVOT function:

select QueueID,
  [0] as Int_0, 
  [1] as Int_1, 
  [2] as Int_2, 
  [3] as Int_3
from
(
  select QueueID, interval_num, TotalCases
  from Case_Process_Status_Hourly_Fact a  
  where a.Enterprise_Date_Key = @enterpriseDateKey   
) src
pivot
(
  max(TotalCases)
  for interval_num in ([0], [1], [2], [3])
) piv

Keep in mind that if you have an unknown number of interval_num values you can also implement dynamic sql to perform this data transformation.

score 0 · Answer 3 · answered Dec 26 '12 at 15:58

0

please take a look at

http://stackoverflow.com/questions/6267660/sql-query-to-convert-rows-into-columns

it might be helpful for you

Or look for T-SQL on the web. it can help you.

answered Dec 26 '12 at 15:58

adeel iqbal

494
5
23

Thank you for the suggestions. I attempted to use the link provided but it did lead me anywhere. I then searched stack overflow with "sql-query-to-convert-rows-into-columns" and found some results that I can read up on. – Patrick Dec 26 '12 at 16:18

Group Differently Or Change to Multiple Subqueries

3 Answers3