-1

I have a sales table and I need to get the sales for each customer in just one row. Example, currently is showing this:

Customer Product Amount
1 Pizza 10
1 Burger 5

I need to show something like this:

Customer Product Amount Product2 Amount2
1 Pizza 10 Burger 5
Al Simmons
  • 17
  • 5
  • 2
    Why do you want to do this? This is nothing short of painful to deal with. What happens when you have a third row? Or a thousandth row? Why is pizza first? – Sean Lange Aug 30 '22 at 15:08
  • 1
    A RDBMS is there to provide query output data and not to lay out, arrange or paint over the results. What you want to do is possible using the PIVOT operator present on some RDBMS like MS SQL Server, but when it comes to cosmetics, it is strongly discouraged! – SQLpro Aug 30 '22 at 15:12
  • 1
    Showing stuff is the job of the client application, not the database. It's a *lot* easier to create a horizontal report than create a query that detects all possible rows, calculates the column names, constructs a dynamic SQL with all that and executes it – Panagiotis Kanavos Aug 30 '22 at 15:13
  • 1
    You can't have an arbitrary number of columns in a query. Tables and columns are like types and properties in a strongly typed language. They must be known when the query gets compiled. – Panagiotis Kanavos Aug 30 '22 at 15:16

1 Answers1

-1

This will get the results you want, but without further definition, there's no guarantee this will work with other examples. A PIVOT is pretty likely a better option, but this method can work as well if your parameters are pretty confined.

select customer
, max(case when prod = 'Pizza' then prod end) as product
, max(case when prod = 'Pizza' then amount end) as amount
, max(case when prod = 'Burger' then prod end) as Product2
, max(case when prod = 'Burger' then amount end) as Amount2
 from table
group by customer
jw11432
  • 545
  • 2
  • 20
  • Very well could be. Depends on the depth of the data. But this does come out in one row and I think I was pretty clear, at least in implication, that this has a pretty reserved use. – jw11432 Aug 30 '22 at 15:45
  • 1
    Ok, now it does. Yes, I forgot about the resultant nulls. – jw11432 Aug 30 '22 at 17:59