SQL query to group by a column and show its result in rows with group by another column and display results in rows

Question

I want the data in a particular format as grouping on one column it will display its result into rows and also grouping another column and display result in rows corresponding to that grouping of rows.

The data is in the format :

id  type    insertdate             forUser
------------------------------------------
1   Suit    2020-08-24 13:16:42.000 sw
2   Suit    2020-08-24 13:16:49.000 sw
3   Suit    2020-08-24 00:46:31.773 sw
4   jacket  2020-08-24 13:17:15.000 sw
5   jacket  2020-08-24 13:17:35.000 sw
6   jacket  2020-08-24 00:47:43.620 sw
7   jacket  2020-08-24 00:48:42.470 SK
8   Blazer  2020-08-24 13:19:19.000 SK
9   Blazer  2020-08-24 00:49:00.097 SK
10  Blazer  2020-08-24 00:49:07.130 SK
11  Blazer  2020-08-24 13:20:34.000 SK
12  indo    2020-08-24 13:21:15.000 SK
13  indo    2020-08-24 13:21:46.000 SK
14  indo    2020-08-24 13:21:59.000 SK

and I want the result in this format :

forUser suit jacket blazer indo
--------------------------------
SK      0     1     4       3
sw      3     3     0       0

I have no idea how to achieve it, can we use count with where clause:

select forUser, count(* where type='suit') 
from tblTurnSys
group by forUser

This technique of turning rows into columns is called a [pivot](https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/). — SMor, Aug 24 '20 at 15:25

score 2 · Answer 1 · answered Aug 24 '20 at 15:19

2

You can use conditional aggregation:

select forUser,
       sum(case when type = 'suit' then 1 else 0 end) as suit,
       sum(case when type = 'jacket' then 1 else 0 end) as jacket,
       sum(case when type = 'blazer' then 1 else 0 end) as blazer,
       sum(case when type = 'indo' then 1 else 0 end) as indo
from t
group by forUser;

answered Aug 24 '20 at 15:19

Gordon Linoff

1,242,037
58
646
786

what if we have unknown number of users? – Sowmyadhar Gourishetty Aug 24 '20 at 15:23
but the column [type] can have more than 4 different items – Juned Khan Aug 24 '20 at 15:25
Do you mean users, or types, @SowmyadharGourishetty . This will have no problems for any number of users. – Thom A Aug 24 '20 at 15:25
sorry it is `type` @Larnu – Sowmyadhar Gourishetty Aug 24 '20 at 15:26
@SowmyadharGourishetty we have group by clause,it will take care. – Juned Khan Aug 24 '20 at 15:26
what if we have another type `test1, test2 .... testn`? – Sowmyadhar Gourishetty Aug 24 '20 at 15:27
1

Then you need a dynamic pivot, @SowmyadharGourishetty , as demonstrated in the duplicate. How to pivot data (dynamically) has been ask 1000's of times on Stack Overflow, and the answer is still this (more or less) the same. – Thom A Aug 24 '20 at 15:29

SQL query to group by a column and show its result in rows with group by another column and display results in rows

1 Answers1