Three dimensional array in SQL Server

Question

I apologize in advance if the title of this question is not right but I do not know how to word it. What I am trying to do is:

I want to show a table on this format.

                  week_1      week_2         week_3
sub_account_1     25 calls    35 calls       15 calls
sub_account_2     35 calls    17 calls       13 calls      
sub_account_3     26 calls    5 calls       11 calls

I am trying something like:

SELECT count(calls), date, sub_account_name
WHERE master_account = 12
AND date IN ('2017-01-07','2017-01-14','2017-01-21')
GROUP BY sub_account_name, date

This query is not showing what I want, instead is showing:

sub_account_1    week_1   35 calls
sub_account_1    week_2   25 calls
sub_account_1    week_3   17 calls
sub_account_2    week_1   13 calls
sub_account_2    week_2   11 calls 
......
......
sub_account_3    week_3    15 calls

I would appreciate if someone can help me how to write the query to produce the desired table.

Edit to show sample data:

There are two tables (Account and Call)

Account
ID           int
AccountId    int
Name         varchar(50)
master       int

master accounts and subaccounts are both same datatype and stored on the same table. If an account has accountId = master it means that it is a master account. All its subaccount will hold the accountId value of their master as a master value.

Call
ID            int
callId        int
date          datetime
f_accountId   int

I need all the calls on the following dates ('2017-01-07','2017-01-14','2017-01-21') on the format showed above.

sounds like you need a pivot, but you need to show the current data sample (i.e. what the data looks like before your query) to be sure. — S3S
– S3S, Commented Apr 17, 2017 at 19:57
Here is a great place to start. spaghettidba.com/2015/04/24/… — Sean Lange
– Sean Lange, Commented Apr 17, 2017 at 20:12

Jason · Accepted Answer · 2017-04-17 21:19:55Z

1

SELECT Name,[14] as 'Week 1',[15] as 'Week 2',[16] as 'Week 3'
FROM (SELECT [Name],DATEPART (WK,c.date) as 'Week'   
    FROM Account a  --Highlight this subquery to determine the week numbers
        JOIN [CALL] c ON a.AccountId = c.f_accountId
    WHERE [master] = 2
     ) p
PIVOT
(
COUNT(WEEK)
FOR WEEK IN ([14],[15],[16]) --These numbers are the week numbers
) as pvt

answered Apr 17, 2017 at 21:19

Jason

9531 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DoArNa Over a year ago

Thank you. This works perfect. I knew nothing about PIVOT in SQL. I will have to read some materials about this.

Collectives™ on Stack Overflow

Three dimensional array in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related