-1

I'm creating a custom SQL view in which we can find the quantities of items by location. At the moment it provides the information perfectly in columns, however can I provide this information with a subquery or aliasing to have that information in a row?

I've tried a select subquery, however it returns

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT SKU, DESCRIPTION, QUANTITY, LOCATION
FROM INVENTORY

Output:

RB  RED BALL    0   OVERSTOCK
RB  RED BALL    1   ROOM 1
RB  RED BALL    3   ROOM 2
RB  RED BALL    5   ROOM 3

I would love to see:

SKU     DESCRIPTION    QTYOVERSTOCK QTYROOM1 QTYROOM2 QTYROOM3
--------------------------------------------------------------
RB      RED BALL            0           1       3        5
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Graeme Martin
  • 13
  • 4
  • Search SQL Server PIVOT, or use `SELECT ... MAX(CASE END) AS QTYROOM1... GROUP BY SKU, DESCRIPTION` "pivotting".. This question is a duplicate [SQL server pivot](https://stackoverflow.com/search?q=SQL+server+pivot) for sure – Raymond Nijland Jul 10 '19 at 15:07
  • Looks like you need a PIVOT query. – Robert Kock Jul 10 '19 at 15:08

1 Answers1

2

You just need a basic pivot query:

SELECT
    SKU,
    DESCRIPTION,
    MAX(CASE WHEN LOCATION = 'OVERSTOCK' THEN QUANTITY END) AS QTYOVERSTOCK,
    MAX(CASE WHEN LOCATION = 'ROOM 1' THEN QUANTITY END) AS QTYROOM1,
    MAX(CASE WHEN LOCATION = 'ROOM 2' THEN QUANTITY END) AS QTYROOM2,
    MAX(CASE WHEN LOCATION = 'ROOM 3' THEN QUANTITY END) AS QTYROOM3
FROM INVENTORY
GROUP BY
    SKU,
    DESCRIPTION;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • This is great Tim! Works like a charm, however when I try to add another perimeter I'm getting an invalid in select list because it's not contained in aggregate function or the group by clause....Do you know a way around this one? – Graeme Martin Jul 10 '19 at 15:19
  • 2
    If you want to add another column to the select clause, then also add it to the group by clause. – Tim Biegeleisen Jul 10 '19 at 15:29