Pivot/UNPIVOT/CROSS APPLY rows and columns

Transact-SQL
1

I know there are many topics on this and have been trying to read through them and get this sql to work but with no luck. I many be approaching this the wrong way so a little insight will be appreciated.

DECLARE @T AS TABLE(Machine NVARCHAR(6), Crew NVARCHAR(20), Yards DECIMAL(10,2));

INSERT INTO @T (Machine, Crew, Yards) VALUES('BL1', '1st Crew', 579.00);
INSERT INTO @T (Machine, Crew, Yards) VALUES('BL1', '2nd Crew', 0);
INSERT INTO @T (Machine, Crew, Yards) VALUES('BL4', '1st Crew', 2950.00);
INSERT INTO @T (Machine, Crew, Yards) VALUES('BL4', '2nd Crew', 1206.00);
INSERT INTO @T (Machine, Crew, Yards) VALUES('BL6', '1st Crew', 434.20);

WITH Ranked AS
(
   SELECT   
			T.*,
			rn = ROW_NUMBER() OVER (
			PARTITION BY T.Crew 
			ORDER BY T.Crew)
			FROM  @T AS T
)
SELECT 
	R.Machine,
	'1st Crew' =      CASE WHEN R.Crew = '1st Crew' THEN ISNULL(R.Yards, 0) END,
	'2nd Crew' =      CASE WHEN R.Crew = '2nd Crew' THEN ISNULL(R.Yards, 0) END,
	--'Total'	=		  R.Yards 
	'Total'	=		  CASE WHEN R.Machine = 'BL1' THEN R.Yards END
FROM Ranked AS R
GROUP BY R.Machine, R.Crew, R.Yards;

The outcome of the above script is this:

|Machine|1st Crew|2nd Crew|Total|

|BL1| 579.00| NULL| 579.00|
|BL1| NULL| 0.00| 0.00|
|BL4| 2950.00| NULL| NULL|
|BL4| NULL| 1206.00| NULL|
|BL6| 434.20| NULL| NULL|

But it should be this and I can't seem to get a total.

|Machine|1st Crew|2nd Crew|Total|

|BL1| 579.00| 0.00| 579.00|
|BL4| 2950.00| 1206.00| 4156.00|
|BL6| 434.20| NULL| 434.20|

Any suggestions? Thanks.

2

Group it only by Machine. When you do that, the code is going to complain because you have columns in the select list that are not in the group by clause - Crew and Yards columns. To eliminate that error, you have to make sure that references to those columns are within an aggregate function. I have used SUM in the example below. That may or may not be what your requirements call for.

WITH Ranked AS
(
   SELECT   
			T.*,
			rn = ROW_NUMBER() OVER (
			PARTITION BY T.Crew 
			ORDER BY T.Crew)
			FROM  @T AS T
)
SELECT 
	R.Machine,
	'1st Crew' =      SUM(CASE WHEN R.Crew = '1st Crew' THEN ISNULL(R.Yards, 0) END),
	'2nd Crew' =      SUM(CASE WHEN R.Crew = '2nd Crew' THEN ISNULL(R.Yards, 0) END),
	--'Total'	=		  R.Yards 
	'Total'	=		  SUM(R.Yards)
FROM Ranked AS R
GROUP BY R.Machine;
3

Grouping only by Machine is needed but the other columns are needed to avoid the error. And they have to be included in the select in order to get the information that is needed. So I need a better solution.

4

try a pivot

Create table #T (Machine NVARCHAR(6), Crew NVARCHAR(20), Yards DECIMAL(10,2));

INSERT INTO #T(Machine, Crew, Yards) VALUES('BL1', '1st Crew', 579.00);
INSERT INTO #T(Machine, Crew, Yards) VALUES('BL1', '2nd Crew', 0);
INSERT INTO #T(Machine, Crew, Yards) VALUES('BL4', '1st Crew', 2950.00);
INSERT INTO #T(Machine, Crew, Yards) VALUES('BL4', '2nd Crew', 1206.00);
INSERT INTO #T(Machine, Crew, Yards) VALUES('BL6', '1st Crew', 434.20);

;

DECLARE @Colslist VARCHAR(MAX),
		@TotalList varchar(max)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT Crew  
FROM #T
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  ,
		@TotalList = COALESCE(@TotalList + ' + IsNull([', 'IsNull([') + Head + '],0)' 
FROM @Cols t  


EXEC ('SELECT *, ' + @TotalList + ' as Total
FROM   
(  
        SELECT T.Machine,t.crew, t.yards
        FROM #t t 
) t  
PIVOT (sum(Yards) FOR crew IN (' + @ColsList + ')) PVT')
5

What about

Select machine,max(1st crew) , max(2nd crew) , max(1st crew) + max(2nd crew)
Group by machine

Hope I am right
:grinning::grinning:

6

You need the other columns in the select, but you don't need them in the group by clause. That is why I put the other columns inside an aggregate function - SUM. Whether SUM is the appropriate aggregate function or not is something that you can determine based on your business rules.

Copy the query I posted and try running it.

Just to be clear, there is NO "better" solution in SQL. From Microsoft documentation: "... each table or view column in any nonaggregate expression in the <select> list must be included in the GROUP BY list"

7

You're right. I didn't notice you placed the yards with the SUM function. It worked. Thanks.

8

did the pivot not work? It's more flexible as it isn't hardcoded to 1st or 2nd crew.

9

Yes, the pivot worked as well. I'm testing both against the database for efficiency. Thanks !!!

10

what if tomorrow 50 crews come? :wink:

11

google search
find how to article
do

:slight_smile:
:slight_smile: