CODEWITHSUNDEEP
sqlsql-serverpivot
Sree
Sree

Reputation: 75

Select Single field multiple times with multiple criteria

I need to generate a Project wise surface area at each activity from below table PLE

Activity    Posting Date    Surface Area  Project
---------------------------------------------------------------------
Shearing     01-04-2013     2.34          A   
Bending      01-04-2013     2.34          A 
Assembly     02-04-2013     2.34          B
PC           02-04-2013     5.34          B  
Infill       05-04-2013     5.34          C

I'm trying to do this.

SELECT DISTINCT Project,sum(Project.[Surface Area]) AS TotalShearing 
FROM PLE
WHERE ([Posting Date] BETWEEN @StartDate AND @EndDate)
  AND (Activity = Shearing)
GROUP BY Project

Now I want to display TotalBending, TotalAssembly and so on in columns right to TotalShearing. But no idea how to get them as WHERE condition is already used for Activity 'Shearing'. This may be simple task, but I'm new to SQL and hence need HELP!

Upvotes: 3

Views: 135

Answers (4)

Matthias
Matthias

Reputation: 164

The thing u want is the "Partition By" Keyword in SQL. To be more precise Sum(....) over(Partition by Activity). That should do the trick.

Upvotes: 0

Krishna Rani Sahoo
Krishna Rani Sahoo

Reputation: 1539

SELECT DISTINCT Project,
sum(if(Activity = 'Shearing', Project.[Surface Area],0) AS Shearing ,
sum(if(Activity = 'Bending', Project.[Surface Area],0) AS TotalBending, 
sum(if(Activity = 'Assembly', Project.[Surface Area],0) AS TotalAssembly
FROM PLE
WHERE ([Posting Date] BETWEEN @StartDate AND @EndDate)

GROUP BY Project

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Use the PIVOT table operator:

SELECT *
FROM 
(
  SELECT Activity, [Surface Area], project
  FROM PLE
) AS t
PIVOT
(
  sum([Surface Area])
  FOR Activity IN ([Shearing], 
                   [Bending], 
                   [Assembly], 
                   [PC],
                   [Infill])
) AS p;

If you want to do this dynamically for each number of Activity you have to use dynamic SQL like this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);


select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(Activity)
                      FROM PLE
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT *
FROM 
(
  SELECT Activity, [Surface Area], project
  FROM PLE
) AS t
PIVOT
(
  sum([Surface Area])
  FOR Activity IN (' + @cols + ')
) AS p';

execute(@query);

See this:

Upvotes: 1

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Try something like this:

SELECT Project
    ,sum(CASE WHEN Activity = 'Shearing' THEN [Surface Area] ELSE 0 END) AS TotalShearing 
    ,sum(CASE WHEN Activity = 'Bending' THEN [Surface Area] ELSE 0 END) AS TotalBending
    ,sum(CASE WHEN Activity = 'Assembly' THEN [Surface Area] ELSE 0 END) AS TotalAssembly
    ,sum(CASE WHEN Activity = 'PC' THEN [Surface Area] ELSE 0 END) AS TotalPC
    ,sum(CASE WHEN Activity = 'Infill' THEN [Surface Area] ELSE 0 END) AS TotalInfill
WHERE ([Posting Date] BETWEEN @StartDate AND @EndDate)
FROM PLE
GROUP BY Project

SQLFiddle DEMO

Upvotes: 0

Related Questions