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.

Answer 1

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