CODEWITHSUNDEEP
sqlpivotrowsmultiple-columns
user2051103
user2051103

Reputation: 21

SQL, multiple rows to one column

So I have the following date

ID       NAME       MONTH              COUNT

1        David      December2012       500

2        Rob        December2012       320

1        David      January2013        400

2        Rob        January2013        280

I am trying to make this.......

ID       Name       December2012       January2013

1        David      500                400

2        Rob        320                280

Where I get confused is how I want to keep two of the columns and just pivot the two other fields. Anyone know how I would do this.

Thank you so much for your help/time. I have never posted one of these, and responses are greatly appreciated!

Upvotes: 1

Views: 3097

Answers (2)

Taryn
Taryn

Reputation: 247710

You did not specify what RDBMS you are using. You can pivot the data in all databases using an aggregate function with a CASE expression:

select id, name, 
  sum(case when month = 'December2012' then "count" end) December2012,
  sum(case when month = 'January2013' then "count" end) January2013
from yourtable
group by id, name

See SQL Fiddle with Demo

If you are using SQL Server 2005+ or Oracle 11g then you can use the PIVOT function:

select *
from
(
  select id, name, month, [count]
  from yourtable
) src
pivot
(
  sum([count])
  for month in (December2012, January2013)
) piv

See SQL Fiddle with Demo.

In SQL Server, if the values of the month are unknown then you can use dynamic SQL similar to this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, name,' + @cols + ' from 
             (
                select id, name, month, [count]
                from yourtable
            ) x
            pivot 
            (
                sum([count])
                for month in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

All versions yield the result:

| ID |  NAME | DECEMBER2012 | JANUARY2013 |
-------------------------------------------
|  1 | David |          500 |         400 |
|  2 |   Rob |          320 |         280 |

Upvotes: 5

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Since you didn't specify what RDBMS you are using, then you can do this:

SELECT
  ID,
  NAME,
  MAX(CASE WHEN MONTH = 'December2012' THEN "COUNT" END) AS "December2012",
  MAX(CASE WHEN MONTH = 'January2013'  THEN "COUNT" END) AS "January2013"
FROM Tablename
GROUP BY ID, Name;

Upvotes: 0

Related Questions