Single Column as N Rows

Question

I have a large table with 3 columns as follows:

Invoice Product     Color
1       Pant        Red
1       Pant        Black
1       Shirt       Green
2       Pant        White
2       Pant        Black
2       Pant        Blue

I'd like to group on Invoice & Product and then have all unique Color values appear on the related grouped record as follows:

Invoice Product     Colour1     Colour2     Colour3
1       Pant        Red         Black
1       Shirt       Green
2       Pant        White       Black       Blue

Is this possible in SQL Server?

If that is how your data looks it's impossible to achieve the result you want exactly as there is no way of ensuring the order of colors. If it doesn't matter which color goes into color1-3 then it's easy. Search for sql server & pivot. There are many duplicates here on SO. — jpw, Mar 21 '15 at 01:26
Checkout the [complex pivot example from technet](https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx) — LinkBerest, Mar 21 '15 at 01:29

score 2 · Answer 1 · answered Mar 21 '15 at 01:29

It is possible in SQL Server -- if you know that there are three color columns. If there are a variable number, then it is still possible, but it requires dynamic SQL.

I would approach this using conditional aggregation:

select invoice, product,
       max(case when seqnum = 1 then colour end) as colour1,
       max(case when seqnum = 2 then colour end) as colour2,
       max(case when seqnum = 3 then colour end) as colour3
from (select t.*,
             row_number() over (partition by invoice, product order by (select nULL)) as seqnum
      from table t
     ) t
group by invoice, product;

score 0 · Answer 2 · edited Jan 18 '21 at 12:32

To convert rows into columns, you need to use Pivot in Sql Server. If you know the number of columns in advance, you can use pivoting statically as the answer suggested by Gordin Linoff.

Sometimes, the number of colors may vary(in your example there are only 3 colors). In such case, you cannot hardcode the column names. For that first of all you need to get columns names dynamically into a variable.

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + COLUMNNAME + ']', '[' + COLUMNNAME + ']')
               FROM    
               (
                     SELECT DISTINCT 
                     'COLOR'+CAST(ROW_NUMBER() OVER(PARTITION BY INVOICE,PRODUCT ORDER BY (SELECT 0)) AS VARCHAR(10)) COLUMNNAME
                     FROM #TEMP
               ) PV 
               ORDER BY COLUMNNAME

Now the above variable have values of columns as Comma Separated Values which can be used with IN operator dynamically for the below query. Since your table doesn't have values like COLOR1, COLOR2 etc, I have provided logic to get column names for each INVOICE and its PRODUCT using PARTITION BY clause.

DECLARE @query NVARCHAR(MAX)
SET @query = '-- This outer query forms your pivoted result
             SELECT * FROM 
             (
                -- Source data for pivoting
                SELECT DISTINCT INVOICE,PRODUCT,COLOR,
                ''COLOR''+CAST(ROW_NUMBER() OVER(PARTITION BY INVOICE,PRODUCT ORDER BY (SELECT 0)) AS VARCHAR(10)) COLUMNNAME 
                FROM #TEMP                 
             ) x
             PIVOT 
             (
                 --Defines the values in each dynamic columns
                 MIN(COLOR)
                 -- Get the names from the @cols variable to show as column
                 FOR COLUMNNAME IN (' + @cols + ')
            ) p            
            ORDER BY INVOICE;' 

EXEC SP_EXECUTESQL @query

Click here to view result

Did u get the result? @user3676641 – Sarath Subramanian Mar 24 '15 at 13:56 — Sarath Subramanian, Mar 24 '15 at 13:56

Single Column as N Rows

2 Answers2