Create Pivot query in SQL Server like Excel pivot table

Question

I am trying to convert an Excel pivot to a SQL query so instead of extracting the data first from my SQL Server database then create the pivot table manually in Excel, I want to create the pivot query directly in SQL.

Here is an example of my data and pivot table

I am stuck how to finish my SQL query, my start was :

SELECT [Brand],[Location],[Qty],[Price] FROM ShipmentsTable 
PIVOT 
(
 SUM(Qty),SUM([Price])
 FOR [MFG Location] IN ... --not sure what to add here
)

Any suggestions please how should I update my query ?

Answer 1

You can try to use CASE WHEN and SUM function to make it.

SELECT Brand,
       SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
       SUM(CASE WHEN Location = 'Austria' THEN Price  END) 'Austria_totle',
       SUM(CASE WHEN Location = 'France' THEN qty  END) 'France_qty',
       SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
       SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
       SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
       SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
       SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
FROM T
GROUP BY Brand

sqlfiddle:http://sqlfiddle.com/#!18/90e75/17

Results:

|   Brand | Austria_qty |      Austria_totle | France_qty | France_totle | Germany_qty | Germany_totle | Italy_qty | Italy_totle |
|---------|-------------|--------------------|------------|--------------|-------------|---------------|-----------|-------------|
|   Apple |           1 |            1351.16 |          1 |         9.96 |           2 |       1583.85 |         1 |     1053.83 |
|  Huawei |           1 |             744.67 |     (null) |       (null) |           2 |     207704.86 |    (null) |      (null) |
|  Lenovo |           2 |            1184.21 |          2 |      1420.43 |           2 |       3454.91 |    (null) |      (null) |
|   Nokia |      (null) |             (null) |          1 |       796.03 |      (null) |        (null) |         1 |      538.41 |
| Samsung |      (null) |             (null) |          1 |      3327.14 |      (null) |        (null) |         1 |        9.09 |

Edit

I saw your commit, if you want to get Total that you can try to use with ROLLUP

SELECT COALESCE(Brand, 'Total') Brand,
         SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
         SUM(CASE WHEN Location = 'Austria' THEN Price  END) 'Austria_totle',
         SUM(CASE WHEN Location = 'France' THEN qty  END) 'France_qty',
         SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
         SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
         SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
         SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
         SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
  FROM T
  GROUP BY Brand with ROLLUP

sqlfiddle

Result

|   Brand | Austria_qty |      Austria_totle | France_qty |       France_totle | Germany_qty | Germany_totle | Italy_qty |        Italy_totle |
|---------|-------------|--------------------|------------|--------------------|-------------|---------------|-----------|--------------------|
|   Apple |           1 |            1351.16 |          1 |               9.96 |           2 |       1583.85 |         1 |            1053.83 |
|  Huawei |           1 |             744.67 |     (null) |             (null) |           2 |     207704.86 |    (null) |             (null) |
|  Lenovo |           2 | 1184.2099999999998 |          2 |            1420.43 |           2 |       3454.91 |    (null) |             (null) |
|   Nokia |      (null) |             (null) |          1 |             796.03 |      (null) |        (null) |         1 |             538.41 |
| Samsung |      (null) |             (null) |          1 |            3327.14 |      (null) |        (null) |         1 |               9.09 |
|   Totle |           4 |            3280.04 |          5 | 5553.5599999999995 |           6 |     212743.62 |         3 | 1601.3299999999997 |