Writing multiple group by SQL Query in SQL Server

Question

I have one table User, with column UserId, Country and State. User table data is as below

-------------------------------
| UserId |  Country |  State  |
--------------------------------
| AAA     |  US      |  AR     |
| BBB     |  GB      |  LN     |
| BBB     |  MX      |  MC     |
| AAA     |  MX      |  CH     |
| CCC     |  GB      |  LE     |
| CCC     |          |  IL     |
--------------------------------

Expected result is as below,

---------------------------------------------------------------------
UserId   totalcountry US  MX  GB TotalState   AR LN  MC  CH  LE  IL
----------------------------------------------------------------------
AAA      2             1  1   0    2          1  0   0   1   0   0
BBB      2             0  1   1    2             1   1   0   0   0
CCC      1             0  0   1    0          0  0   0   0   1   1
-----------------------------------------------------------------------

Hello, and welcome to Stack Overflow. Whilst we are delighted to help, we're not a code-writing service. Please clarify the logic for calculating those results, and show what you've tried. — Neville Kuyt, Jun 18 '19 at 16:49
there's a [famous question](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) for Dynamic Pivoting in SQL Server. — Barbaros Özhan, Jun 18 '19 at 16:50

score 0 · Answer 1 · answered Jun 18 '19 at 17:01

You can use PIVIOT to get your desired result as below-

SELECT [User ID],

SUM([US])+SUM([GB])+SUM([MX]) [total country],
SUM([US]) US,SUM([GB]) GB, SUM([MX]) MX,

SUM([AR])+SUM([LN])+SUM([MC])+SUM([CH])+SUM([LE])+SUM([IL]) [Total State],
SUM([AR]) AR, SUM([LN]) LN, SUM([MC]) MC, SUM([CH]) CH, SUM([LE]) LE, SUM([IL]) IL

FROM
(
    SELECT [User id],Country,State
    FROM your_table
) AS P
PIVOT
(
    COUNT(Country)
    FOR Country IN ([US],[GB],[MX])
) PVT

PIVOT(
    COUNT(State)
    FOR State IN ([AR],[LN],[MC],[CH],[LE],[IL])
) PVT2
GROUP BY [User ID]

Writing multiple group by SQL Query in SQL Server

1 Answers1