Write advanced SQL Select

Question

Item table:

|   Item    |   Qnty    |   ProdSched   |
|    a      |    1      |       1       |
|    b      |    2      |       1       |
|    c      |    3      |       1       |
|    a      |    4      |       2       |
|    b      |    5      |       2       |
|    c      |    6      |       2       |

Is there a way I can output it like this using SQL SELECT?

|   Item    |   ProdSched(1)(Qnty)  |   ProdSched(2)(Qnty)  |
|    a      |           1           |       4               |
|    b      |           2           |       5               |
|    c      |           3           |       6               |

Please learn to work your Shift key. Typing IN ALL CAPS IS CONSIDERED SHOUTING (and is rude). typing in all lower case makes thing harder to read. They invented the Shift key for a reason. Thanks. — Ken White, Sep 20 '12 at 01:44
possible duplicate of [Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)](http://stackoverflow.com/questions/12074939/get-rows-as-columns-sql-server-dynamic-pivot-query) — RichardTheKiwi, May 03 '13 at 09:52

Taryn · Answer 1 · 2012-11-02T17:31:06.633

You can use PIVOT for this. If you have a known number of values to transform, then you can hard-code the values via a static pivot:

select item, [1] as ProdSched_1, [2] as ProdSched_2
from 
(
  select item, qty, prodsched
  from yourtable
) x
pivot
(
  max(qty)
  for prodsched in ([1], [2])
) p

see SQL Fiddle with Demo

If the number of columns is unknown, then you can use a dynamic pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(prodsched) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT item,' + @cols + ' from 
             (
              select item, qty, prodsched
              from yourtable
            ) x
            pivot 
            (
                max(qty)
                for prodsched in (' + @cols + ')
            ) p '

execute(@query)

see SQL Fiddle with Demo

score 4 · Accepted Answer · answered Sep 20 '12 at 01:45

4

SELECT Item, 
  [ProdSched(1)(Qnty)] = MAX(CASE WHEN ProdSched = 1 THEN Qnty END),
  [ProdSched(2)(Qnty)] = MAX(CASE WHEN ProdSched = 2 THEN Qnty END)
FROM dbo.tablename
GROUP BY Item
ORDER BY Item;

answered Sep 20 '12 at 01:45

Aaron Bertrand

272,866
37
466
490

1

beat me to the non-pivot version – Taryn Sep 20 '12 at 02:03
1

@bluefeet and somehow I just knew you'd sweep in with a pivot version within a few minutes. +1 of course. – Aaron Bertrand Sep 20 '12 at 02:06

David W · Answer 3 · 2012-09-20T02:10:02.477

Let's hit this in two phases. First, although this is not the exact format you wanted, you can get the data you asked for as follows:

Select item, ProdSched, max(qty)
  from Item1
 group by item,ProdSched

Now, to get the data in the format you desired, one way of accomplishing it is a PIVOT table. You can cook up a pivot table in SQL Server as follows:

Select item, [1] as ProdSched1, [2] as ProdSched2
from ( Select Item, Qty, ProdSched
         from item1 ) x
Pivot ( Max(qty) for ProdSched in ([1],[2]))  y

Write advanced SQL Select

3 Answers3

Linked