SQL group rows to columns

Question

I have a table

ID | Customer | Type    | Value |
---+----------+---------+-------+
 1 | John     | Income  |   50  |
 2 | John     | Income  |   20  |
 3 | Mike     | Outcome |  150  |
 4 | Robert   | Income  |  100  |
 5 | John     | Outcome |  300  |

Want a table like that;

        | John | Mike | Robert | 
--------+------+------+--------+
 Income |  70  |   0  |   100  |
 Outcome| 300  | 150  |     0  |

What should be the SQL Query? Thanks

The problem is Customers and Type are not static they are dynamic. What I tried:

  SELECT 'TotalIncome' AS TotalSalaryByDept, 
    [John], [Mike]
    FROM
    (SELECT Customer, Income
     FROM table001) AS a
    PIVOT
    (
     SUM(Income)
     FOR ID IN ([John], [Mike])
    ) AS b;

Have a look at [PIVOT()](https://www.techonthenet.com/sql_server/pivot.php) — JohnHC, Dec 09 '16 at 14:57
show us your pivot query and we'll point out how to get it to work. You really need to be showing this stuff in your questions, from a quick skim of your previous questions there is minimal effort going in on your part. We're not a free coding service, show us your attempts and we'll help. — Rich Benner, Dec 09 '16 at 14:58
Then you need a dynamic pivot query. Have you Googled for that? — Tim Biegeleisen, Dec 09 '16 at 14:59
The question looks very similar to this question, which has a very detailled answer: http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — David Verdin, Dec 09 '16 at 15:03

John Cappelletti · Accepted Answer · 2016-12-09T15:54:46.487

4

Here is a quick dynamic pivot. We use a CROSS APPLY to unpivot the desired measures.

Declare @SQL varchar(max) 
Select  @SQL = Stuff((Select Distinct ',' + QuoteName(Customer) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
 Select [Type],' + @SQL + '
  From (
        Select Item=A.Customer,B.* 
         From  YourTable A
         Cross Apply (
                       Select Type=''Income'' ,Value=A.Income Union All
                       Select Type=''Outcome'',Value=A.Outcome
                     ) B
       ) A
 Pivot (sum(value) For Item in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

EDIT - For the Revised Question

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Customer) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
 Select [Type],' + @SQL + '
  From  (Select Customer,Type,Value from YourTable ) A
 Pivot (Sum(Value) For [Customer] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

edited Dec 09 '16 at 15:54

answered Dec 09 '16 at 15:11

John Cappelletti

79,615
7
44
66

@Guest I don't understand your comment. – John Cappelletti Dec 09 '16 at 15:39
@Guest See Edit/Revised section – John Cappelletti Dec 09 '16 at 15:55
thank you million times. – Guest Dec 09 '16 at 16:16
@Guest Happy to help – John Cappelletti Dec 09 '16 at 16:20

score -1 · Answer 2 · answered Dec 09 '16 at 15:16

-1

The Table as you have it is how it should be in your SQL database. Columns are reserved for classifying your data, and rows are where you add new instances.

What you need to do is set up your ASP, Excel Pivot Table, or whatever you are using to display the data to format it into a horizontal table. I would need to know what you are using to interface with your database to give you an example.

answered Dec 09 '16 at 15:16

Nosajimiki

1,073
1
9
17

edited my question – Guest Dec 09 '16 at 15:44
This answer makes no sense at all. There is nothing about this question that relates to "display" – Hogan Dec 09 '16 at 15:54

SQL group rows to columns

2 Answers2