SQL Number of Selected Rows Pivotish

Question

I have a table in SQL Server, and in this case I'm using two columns:

First is the month of a year,

second is the name of service which a customer has a complain about,

and what I want is to have a matrix which the X would be the name of months, and the Y axis would be the name of services, and A(i,j) would be sum of complains of service Y(j) in month of X(i).

I was planning to use pivot in a query to make that matrix. I was wondering if you could please help with this.

Thanks:)

I am counting a case statement in pivot, so I guess not. Unless I understand the question of the provided link in a wrong way. — Yousef, May 29 '16 at 14:07

score 1 · Accepted Answer · edited Nov 08 '21 at 04:56

You can try this.

SELECT 
    [Service],
    [June] = ISNULL([June], 0),
    [July] = ISNULL([July], 0),
    [August] = ISNULL([August], 0),
    [September] = ISNULL([September], 0)
FROM
(
     SELECT 
        [Service], 
        [Month], 
        COUNT([Month]) AS [Count]
     FROM Table_Name
     GROUP BY [Service], [Month] 
) AS PV
PIVOT
(
   SUM([Count])
   FOR [Month] IN ([June], [July], [August], [September])
) AS RS

UPDATE

There are 3 steps to create pivot table.

Write the Base Query

SELECT [Service], [Month], COUNT([Month]) AS [Count] FROM Table_Name GROUP BY [Service], [Month]

Create the PIVOT Expression

PIVOT ( SUM([Count]) FOR [Month] IN ([June], [July], [August], [September]) )
Add the Column Names to the SELECT List

SELECT [Service], [June] = ISNULL([June], 0), [July] = ISNULL([July], 0), [August] = ISNULL([August], 0), [September] = ISNULL([September], 0)

Try to write PIVOT query using these process, It may help you to write this type query later.

Thanks! It works fine. I was thinking how do you think that you can write this query. If you could please teach me the logic behind this code. If you couldn't thank you so much for this. It was very helpful. What I mean from logic is how to analyze this query in paper to understand it. — Yousef, May 30 '16 at 05:31

score 1 · Answer 2 · answered May 29 '16 at 15:05

1

This is basic pivot statement:

declare @t table(m varchar(10), s varchar(10))
insert into @t values
('june','adsl'),
('july','lte'),
('june','lte'),
('august','landline'),
('september','lte'),
('june','lte'),
('june','lte')

select * from @t
pivot(count(m) for m in([june],[july],[august],[september]))p

http://rextester.com/JYO63999

answered May 29 '16 at 15:05

Giorgi Nakeuri

35,155
8
47
75

1

I run your code and it works fine, but my original table has many column and * show all columns, and when I'm using the name of column I want, it shows only one column. – Yousef May 30 '16 at 05:24
1

@yousefyegane, you could just select columns you want lol, `select s,[june],[july],[august],[september]` – Giorgi Nakeuri May 30 '16 at 06:00

SQL Number of Selected Rows Pivotish

2 Answers2