Convert "flat" SQL query result into table structure

Question

I've got a Sql Table that returns a result as follows:

Amount  Descrip
----------------------------
1.34    Group1  Description1
2.36    Group2  Description2
5.46    Group3  Description3
4.54    Group1  Description4
6.23    Group2  Description5

I need to make it look something like this.

Descrip      Group1 Group2  Group3 
------------------------------------
Description1 1.34
Description2        2.36 
Description3                5.46
Description4 4.54
Description5        6.23

The thing to keep in mind is that there is no set rule that Group 3 will exist or any group for that matter. The list of groups is determined by the query results. I could have Group1, Group2, Group4 and Group6 one time and not have group4 but have group5 another.

I know i can do this in .Net code but it would be time consuming in the code. I'd prefer to do it in the SQL server an just bind to the result set.

As a side comment, if there was a way to rollup the values to get a grand total (like 5.88 for Group1) at the bottom, that would be great. I can do the calc at runt time, but would like to do it server side as well if i can.

Answer 1

look at using pivot

SELECT 'Amount' AS MaxAmount, [Group1], [Group2], [Group3]
  FROM
(SELECT descrip, Amount 
    FROM Table) AS SourceTable
PIVOT
(
Max(Amount)
FOR descrip IN ([Group1], [Group2], [Group3])
) AS PivotTable;

Need to put in the appropriate aggregate function, for this example I used max.

Answer 2

Like SeriousCallersOnly said, you need a pivot statement. Unfortunately, to use those you have to hard-code in the actual values ("Group1", "Group2", "Group3", etc) that are to be pivoted upon. If you can't determine them until runtime, you have to build the queries at runtime, which means building and executing dynamic code. This is not simple to do. If you do have to do this kind of query a lot, you'll [have to] get reasonably good at it; if this is the only place you think you'll ever do it, you might be better off saddling the application side with the work.

(Another possibility: hard-code in all possible pivoted values, and then have the application ignore the "empty" columns. This doesn't work if the pivoted values are random, arbitrary, or user-entered.)

Answer 3

When you want to change row data into columns (AKA pivot), the most widely supported approach is to use CASE statements:

SELECT t.descrip,
       CASE WHEN t.descrip LIKE 'GROUP 1%' THEN t.amount END 'Group 1',
       CASE WHEN t.descrip LIKE 'GROUP 2%' THEN t.amount END 'Group 2',
       CASE WHEN t.descrip LIKE 'GROUP 3%' THEN t.amount END 'Group 3',
       ...etc

This however also means that if you don't have a record for a particular description, the value will display as NULL. SQL is an all or nothing affair - the either the column will be present or it won't in the entire result set.

Depending on your database, there is syntax to help out. For instance, SQL Server has supported the PIVOT function since SQL Server 2005. Oracle added it in 11g. I don't believe MySQL or Postgres have pivot functionality - you'd have to use CASE statements.

As for having dynamic groups, you'd need to use dynamic SQL to accommodate. Neither CASE nor PIVOT functionality can handle this - the output columns have to be defined for the query.