SQL Query to Pivot a Column Using Case When

SQL query to pivot a column using CASE WHEN

SELECT 
  name,
  SUM(CASE WHEN val = 1 THEN amount ELSE 0 END) AS amountVal1,
  SUM(CASE WHEN val = 2 THEN amount ELSE 0 END) AS amountVal2
FROM bank GROUP BY name

How to take multiple columns in PIVOT using case statement

You can try below -

with cte as
(
select tab.Name, count(*) as count, newtype, sum(tab.area) as area
from 
(Select b.ID,b.Name,b.area, 
case when a.type='R' then 'R' when a.type='P' then 'P' else 'V' end newtype
from table1 a
left join 
(select * from table2) b on a.ID=b.ID) as tab
group by newtype, tab.Name
)

select name, max(case when type='P' then countval end) as Pcount,
max(case when type='P' then area end) as PArea,
max(case when type='V' then countval end) as Vcount,
max(case when type='V' then area end) as VArea,
max(case when type='R' then countval end) as Rcount,
max(case when type='R' then area end) as RArea
from cte 
group by name

Change PIVOT column using Case Statement

You might require to modify your @Query assignment as below:

Select @query = 'SELECT date, ' + @cols + ' from 
    (
        select date
            , amount
            , category1
            , category2
        from temp
   ) x
    pivot 
    (
         max(amount)
        for ' +CASE WHEN @NeedByCategory = 0 THEN 'category1' ELSE 'category2' END+ ' in (' + @cols + ')
    ) p '

Changes : changed to Select and moved case to outside of the query

SQL Server pivot using case statement

You need to use aggregate on top of case statements

SELECT id,
       Max(CASE
             WHEN LEFT(areaID, 1) = 1 THEN 'Yes'
           END) Head,
       Max(CASE
             WHEN LEFT(areaID, 1) = 2 THEN 'Yes'
           END) Face,
       Max(CASE
             WHEN LEFT(areaID, 1) = 3 THEN 'Yes'
           END) Neck,
       Max(CASE
             WHEN LEFT(areaID, 1) = 4 THEN 'Yes'
           END) Abdo
FROM   #testcase
GROUP  BY id 

SQL Server - PIVOT on CASE statement

You cannot do that. However, you can do it in the anchor query like this:

Select *
from
(
  select Store, Holiday, Date_Index, 
    CASE WHEN ISNumeric(Temp) = 1 THEN CAST(Temp AS DECIMAL(10, 2)) ELSE 0 END AS Temp
  from myTable
) as t
PIVOT
(
  sum(Temp)
  FOR DATE_INDEX IN ([t_0],[t_1],[t_2])
) as P;

I used the ISNUMERIC built in function to replace those non numeric values with 0 and cast the whole column to be decimal before the pivot. Then in the pivot just use SUM(temp).

demo

Results:

| Store | Holiday |    t_0 |  t_1 |    t_2 |
|-------|---------|--------|------|--------|
|   123 |    XMAS |   34.9 | 22.3 |   28.5 |
|   234 |    XMAS | (null) |    0 | (null) |

Converting row values to columns in result using CASE statement

You can do conditional aggregation with help of case expression :

select id, name, 
       max(case when subject = 'Maths' then mark end) as Maths_mark, 
       max(case when subject = 'Science' then mark end) as Science_mark,
       . . .
from student s 
group by id, name;

How to pivot values into two columns using a CASE statement

Just use conditional aggregation:

SELECT MATERIAL,
       SUM(CASE WHEN STOCK_TYPE = 'A' THEN QUANTITY END) as uncovered_qty,
       SUM(CASE WHEN STOCK_TYPE <> 'A' THEN QUANTITY END) as blank_qty
FROM VW_MRP_ALLOCATION 
WHERE STOCK_TYPE IN ('A', '') AND MATERIAL = '011040'
GROUP BY MATERIAL;

SQL Pivoting with CASE Statement Not Working

You need an aggregation functions, such as max():

SELECT Data,
       MAX(CASE WHEN UserId = 'Joao' THEN CPF END) AS Joao,
       MAX(CASE WHEN UserId = 'Maria' THEN CPF END) AS Maria,
       MAX(CASE WHEN UserId = 'Geovanna' THEN CPF END) AS Geovanna,
       MAX(CASE WHEN UserId = 'Eduardo' THEN CPF END) AS Eduardo,
       MAX(CASE WHEN UserId = 'Julia' THEN CPF END) AS Julia
FROM pivot_teste
GROUP BY Data;

Is There a Way to Automate the Conversion of SQL Rows to Column Using Case?

You shouldn't need to create a column for each name. Your first query is sufficient (would obviously just need to change the limit to 100). Based on the questions tags I'm assuming your using Tableau, so it would be as simple as choosing your desired visualisation (say a bar chart) and placing names on one axis and total on the other axis.

Based on your follow up comment it would look like this

SELECT
name,
year,
SUM(number) AS total
From bigquery-public-data.usa_names.usa_1910_current
WHERE name IN
(
SELECT name
FROM
(
SELECT
  name,
  SUM(number) AS total
FROM
  bigquery-public-data.usa_names.usa_1910_current
WHERE
  year BETWEEN 1910 AND 2020
GROUP BY
  name
ORDER BY
  total DESC
LIMIT
  100
))
GROUP BY name, year

You could also look into using calculate fields within Tableau ok the raw data to achieve the desired visualisation.

---

Related Topics