SQL Server : Crosstab/Pivot Price List

Question

I have spent 2 days trying to get this to work but am not getting anywhere close to resolving this.

I have a brought the data together to make a PriceList view

| Category     | Product     | QtyFrom | QtyTo   | Price   |
------------------------------------------------------------
| Category#1   | Product#1   | 1       | 9       | 4.99    |
| Category#1   | Product#2   | 1       | 9       | 5.99    |
| Category#1   | Product#3   | 1       | 9       | 6.99    |
| Category#1   | Product#1   | 10      | 49      | 4.75    |
| Category#1   | Product#2   | 10      | 49      | 5.75    |
| Category#1   | Product#3   | 10      | 49      | 6.75    |
| Category#1   | Product#1   | 50      | 99      | 4.50    |
| Category#1   | Product#2   | 50      | 99      | 5.50    |
| Category#1   | Product#3   | 50      | 99      | 6.50    |
| Category#1   | Product#1   | 100     | 999     | 4.25    |
| Category#1   | Product#2   | 100     | 999     | 5.25    |
| Category#1   | Product#3   | 100     | 999     | 6.25    |
| Category#2   | Product#4   | 1       | 9       | 4.99    |
| Category#2   | Product#5   | 1       | 10      | 5.99    |
| Category#2   | Product#6   | 1       | 9       | 6.99    |
| Category#2   | Product#4   | 10      | 49      | 4.75    |
| Category#2   | Product#5   | 11      | 50      | 5.75    |
| Category#2   | Product#6   | 10      | 49      | 6.75    |
| Category#2   | Product#4   | 50      | 99      | 4.50    |
| Category#2   | Product#5   | 51      | 99      | 5.50    |
| Category#2   | Product#6   | 50      | 99      | 6.50    |

I need to cross tab this to produce a price list. The end result will be queried by individual category which needs to return results like

| Category#1   |  1  |  9  |  10  |  49  |  50  |  99  |
--------------------------------------------------------
| Product#1    |    4.99   |     4.75    |     4.50    |
| Product#2    |    5.99   |     5.75    |     5.50    |
| Product#3    |    6.99   |     6.75    |     6.50    |

or like

| Category#2   |  1  |  9  |  1  |  10  |  10  |  49  |
--------------------------------------------------------
| Product#4    |    4.99   |             |     4.75    |
| Product#5    |           |     5.99    |             |
| Product#6    |    6.99   |             |     6.75    |

As you can see depending on who enters the data they may make a mistake and misalign the price breaks within a group. But I still need to return the 3 lowest QtyFrom/QtyTo rows (Once I have this data I will be producing a separate report showing just the price break quantities per category so they can run that before producing the price list and align the price breaks)

Some categories might not have 3 price breaks and other may have more. But I always need 3 columns.

Using SQL Server 2014

Thank you in advance.

are you trying to join the two table based on product column? — UserYmY, Feb 15 '15 at 10:02
@Mee There is only 1 view now to work with. the 2 examples are what i need from the data in the view. — Jim, Feb 15 '15 at 10:18
@Mihai I have not saved any i have done. My first few i was trying derived tables to show the MIN of QtyFrom using the same product and category, but i could not get it to work with QtyFrom and QtyMin. Last attempt was with derived tables and derived columns to do the same kind of thing but still unable to think how to get it all to work for the 3 columns returning the QtyFrom and QtyTo and then matching for the rows — Jim, Feb 15 '15 at 10:20

Pரதீப் · Accepted Answer · 2015-02-15T10:29:57.787

Did you try using conditional Aggregate

SELECT Product,
       Max(CASE WHEN QtyFrom >= 1 AND QtyTo <= 9 THEN Price END) [1 | 9],
       Max(CASE WHEN QtyFrom >= 10 AND QtyTo <= 49 THEN Price END) [10 | 49],
       Max(CASE WHEN QtyFrom >= 50 AND QtyTo <= 99 THEN Price END) [50 | 99]
FROM   Yourtable
WHERE  Category = 'Category#1'
GROUP  BY Product

Or Use Pivot. In pivot source query you need to create the range column to use it in pivot column list.

SELECT *
FROM  (SELECT CONVERT(VARCHAR(50), QtyFrom) + ' | '
              + CONVERT(VARCHAR(50), QtyTo) AS Rang,
              Product,
              price
       FROM   Yourtable
       WHERE  Category = 'Category#1') a
      PIVOT (Max(price)
            FOR Rang IN ([1 | 9],
                         [10 | 49],
                         [50 | 99]))piv

Update : If you don't know the price breaks then use Dynamic Pivot

DECLARE @cols VARCHAR(max)='',
        @sql  NVARCHAR(max)

SET @cols = (SELECT DISTINCT '[' + CONVERT(VARCHAR(50), QtyFrom) + ' | '
                             + CONVERT(VARCHAR(50), QtyTo) + '],'
             FROM   Yourtable
             WHERE  Category = 'Category#1'
             FOR xml path(''))

SELECT @cols = LEFT(@cols, Len(@cols) - 1)

SET @sql = 'SELECT *
    FROM  (SELECT CONVERT(VARCHAR(50), QtyFrom) + '' | ''
                  + CONVERT(VARCHAR(50), QtyTo) AS Rang,
                  Product,
                  price
           FROM   Yourtable
           WHERE  Category = ''Category#1'') a
          PIVOT (Max(price)
                FOR Rang IN (' + @cols + '))piv '

EXEC Sp_executesql  @sql

This will only work when knowing the price breaks, they change per category. — Jim, Feb 15 '15 at 10:23
Thank you very much, had a problem with sorting on the columns but managed to fix that. — Jim, Feb 15 '15 at 11:48

SQL Server : Crosstab/Pivot Price List

1 Answers1