SQL Group Count of an item ordered with dynamic column numbers

Question

SQL Server 2012. I wonder if anyone can understand this problem.

I want to find all the different combinations that people ordered. I am not interested if a person bought A and B, and another ordered B and then A. I want the number of columns to be dynamic. The maximum number of rows that the order has.

Order example

Order   Row Art
1       1   A
1       2   B
1       3   C
2       1   A
2       2   B
3       1   C
3       2   D
4       1   A
4       2   B
5       1   B
5       2   A

And the result I want is like this

Count   Art1    Art2    Art3
3       A       B   
1       C       D   
1       A       B       C

Answer 1

Here's the approach:

• First get all the items in the column (in an orderly way)
• The second group on this column to get the score
• select as columns

Below is the request

--Create table t1 ([order] int, row int, art varchar(30))

--insert into t1 values 
--(1,1,'A'),
--(1,2,'B'),
--(1,3,'C'),
--(2,1,'A'),
--(2,2,'B'),
--(3,1,'C'),
--(3,2,'D'),
--(4,1,'A'),
--(4,2,'B'),
--(5,1,'B'),
--(5,2,'A')

    select 
        count(1) as [count], 
        articles,
        max(numberOfArticles) as numberOfArticles
    from 
    (
        select 
        [order],
        count(1) as numberOfArticles,
        Stuff(          
                (
                SELECT 
                    ',' + art 
                FROM t1 AS i
                WHERE i.[order]=o.[order]
                ORDER by art asc
                FOR XML PATH ('')
                ), 
            1, 1, '') as articles
        from t1 o
        group by [order]
    )t
    group by articles

--drop table t1

Conclusion:

Answer 2

PIVOT, , GROUP BY, :

SELECT COUNT(*), Art1, Art2, Art3
FROM (
   SELECT [Order], [1] AS Art1, [2] AS Art2, [3] AS Art3
   FROM 
   (
      SELECT [Order], Art, 
             ROW_NUMBER() OVER (PARTITION BY [Order] 
                                ORDER BY Art) AS [Row]
      FROM mytable
   ) AS src
   PIVOT 
   (
      MAX(Art) FOR [Row] IN ([1], [2], [3])
   ) AS pvt    
) AS t
GROUP BY Art1, Art2, Art3

ROW_NUMBER: Row, Art Order.

Answer 3

select count(*),replace(Art,' ','') from
(select distinct parent_tb.Orderer,
stuff((select','+Art  from Test sun
where sun.Orderer=parent_tb.Orderer
for xml path('')),1,1,'') Art
from Test parent_tb
) TMp
group by Art