Is it possible to create this kind of query in SQL (SQL Server 2012)?

Question

Pls, let me know if is possible to create this kind of query. I never saw something like that

Pls, check below the image. It´s exactly what I need to do!

enter image description here

Are you generating a report? Any query result from a database has a 'cell' for each intersection of a row and column, even if that causes duplicated information (to human eyes) - and, unfortunately, there's just nothing you can do to change that. — Rikalous, Sep 09 '13 at 19:35

Roman Pekar · Accepted Answer · 2013-09-09T20:03:09.950

0

select
    A.OrderID,
    A.CustomerID,
    stuff(
        (select ' ' + T.Product
         from test as T
         where T.OrderID = A.OrderID and T.CustomerID = A.CustomerID
         for xml path(''), type
        ).value('.', 'nvarchar(max)')
    , 1, 1, '') as Product
from test as A
group by A.OrderID, A.CustomerID

sql fiddle demo

If you have Product names in other table:

select
    A.OrderID,
    A.CustomerID,
    stuff(
        (select ' ' + P.Name
         from test as T
             inner join Products as P on P.ID = T.ProductID
         where T.OrderID = A.OrderID and T.CustomerID = A.CustomerID
         for xml path(''), type
        ).value('.', 'nvarchar(max)')
    , 1, 1, '') as Product
from test as A
group by A.OrderID, A.CustomerID

edited Sep 09 '13 at 20:03

answered Sep 09 '13 at 19:35

Roman Pekar

107,110
28
195
197

@RomanPekar Perfect! But now, imagine that instead the product name, I have the product ID that is a foreign key from table Product How can I do the same thing getting the product name from other table? – Rodrigo Abib Sep 09 '13 at 19:53
@RodrigoAbib you can just join it, I'll change an answer in a minute – Roman Pekar Sep 09 '13 at 19:56
@RodrigoAbib see updated, you can do something like this – Roman Pekar Sep 09 '13 at 20:04

Is it possible to create this kind of query in SQL (SQL Server 2012)?

1 Answers1