SQL query create cross column

Question

I have this table

customer |  product | quantity
-------------------------------
CLI01    | A        | 10
CLI01    | B        | 20
CLI02    | A        | 31
CLI03    | A        | 10
CLI03    | C        | 12

and I want to create in SQL Server this output:

customer | crossProduct | quantity
-----------------------------------
CLI01    | A+B          | 30
CLI02    | Only A       | 31
CLI03    | B+C          | 22

Thanks in advance

Niko

Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Juan Carlos Oropeza, Feb 21 '17 at 15:24

score 1 · Answer 1 · answered Feb 21 '17 at 15:22

If you only care about two products, then this is simple aggregation:

select customer,
       (case when count(distinct product) > 2 then 'Lots of Products'
             when min(product) = max(product) then 'Only ' + min(product)
             else min(product) + '+' + max(product)
        end) as crossproduct,
       sum(quantity)
from t
group by customer;

If you care about more than two products, then you'll need to do aggregation string concatenation. That is a bit painful in SQL Server. Start by Googling "sql server aggregate string concatenation".

score 0 · Accepted Answer · answered Feb 21 '17 at 15:30

This is s sample:

----- Test Data ----------
DECLARE @TestData TABLE (customer VARCHAR(10),product VARCHAR(10),quantity INT)
INSERT INTO @TestData
SELECT 'CLI01','A',10 UNION ALL
SELECT 'CLI01','B',20 UNION ALL
SELECT 'CLI02','A',31 UNION ALL
SELECT 'CLI03','A',10 UNION ALL
SELECT 'CLI03 ','C',12
-----  Query -------------
SELECT customer,CASE WHEN COUNT( DISTINCT t.product)=1 THEN 'Only ' ELSE '' END + LEFT(c.product,LEN(c.product)-1) AS Product,SUM(quantity) AS quantity 
FROM @TestData AS t
CROSS APPLY(SELECT a.product+'+' FROM @TestData AS a WHERE a.customer=t.customer FOR XML PATH('')) c(product)
GROUP BY customer,c.product
ORDER BY t.customer

customer    Product quantity
CLI01   A+B 30
CLI02   Only A  31
CLI03   A+C 22

score 0 · Answer 3 · answered Feb 21 '17 at 15:43

Have you tried using stuff? This will give you what you need. Works with as many products as necessary, from sql 2008 onwards.

CREATE TABLE x (customer VARCHAR (20), product CHAR(1), quantity INT )
INSERT INTO x
  VALUES( 'CLI01', 'A', 10),
        ( 'CLI01', 'B', 20),
        ( 'CLI02', 'A', 31),
        ( 'CLI03', 'A', 10),
        ( 'CLI03', 'C', 12)

SELECT  x1.customer, x3.Products, SUM(x1.quantity)
FROM    x x1
        CROSS APPLY ( SELECT  Products = STUFF( (select '+' + product AS [text()]
                      FROM    x  x2
                      WHERE   x2.customer = x1.customer
                      FOR XML PATH ('') ), 1, 1,'') ) x3
GROUP BY x1.customer, x3.Products

thank you guys. steve you query it's perfect. i have add 2 DISTINCT into CROSS APPLY. — NikoG, Feb 21 '17 at 16:34

SQL query create cross column

3 Answers3