-1

I have been looking for the solution to this in PIVOT, UNPIVOT, and others but still don't see my scenario. I have items in a table. For simplicity we'll just say PartNum, Desc. These things can be customized. The attributes like color, height, width, depth are stored in a separate table with a code to indicate which attribute.

OrderId - PartNum - Desc (join from inv)
1         12345   - Block A
2         12345   - Block A
3         23456   - Block B
4         23456   - Block B

Two customers get 12345, and two get 23456 and they have width, height, and depth...

AttrId - OrderId - CCode - Value
1        1         WIDTH   10
2        1         HEIGHT  10
3        1         DEPTH   1
4        2         WIDTH   20
5        2         HEIGHT  10
6        2         DEPTH   1
7        3         WIDTH   10
8        3         HEIGHT  20
9        3         DEPTH   2
10       4         WIDTH   10
11       4         HEIGHT  20
12       4         DEPTH   2

I can't use pivot with an aggregate on the value because I need to group each combination of part, width, height, and depth like this

PartNum - Width - Height - Depth - Count - Area (w x h x count)
12345     10      10       1       1       100
12345     20      10       1       1       200
23456     10      20       2       2       400

I tried case statements with the CCode but I get null values in some rows so the grouping didn't work. This is in SQL Server 2019 if that makes a difference. Can someone help out with this?

Hey Mikey
  • 101
  • 6

1 Answers1

1

Is this what you want?

select t1.partnum, t2.width, t2.height, t2.depth, count(*) as cnt
from t1 join
     (select t2.orderid,
             sum(case when ccode = 'width' then value end) as width,
             sum(case when ccode = 'height' then value end) as height,
             sum(case when ccode = 'depth' then value end) as depth
      from t2
      group by t2.orderid
     ) t2
     on t2.orderid = t1.orderid
group by t1.partnum, t2.width, t2.height, t2.depth;

I might speculate that you want:

sum(t2.width * t2.height * t2.depth) as area

but the numbers disagree with the values in your question.

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I would guess (width * height * count) = area – Paul Maxwell Mar 29 '21 at 00:14
  • This doesn't really accomplish what I'm looking for since the length, width, and depth are the sum of all of them instead of the actual values. I get 20 x 40 on line 3 instead of 10 x 20 when I run this on the sample data. (BTW area is surface area and not volume, hence depth excluded). The values presented to the user are supposed to be size of each and qty i.e. 1 @ 10 x 10, 1 @ 12 x 10, 2 @ 10 x 20 – Hey Mikey Mar 29 '21 at 01:26
  • @HeyMikey . . . There is no 20x40 in the result set. I've shown that it work on your sample data -- well, except for `area` which is not explained in your question. – Gordon Linoff Mar 29 '21 at 01:35
  • You're right. I had a eureka moment just now. I wasn't grouping right on the inner select. This works perfectly. I just noticed my typo in the second row for area s/b 12x10 =120. – Hey Mikey Mar 29 '21 at 01:58