| Author |
Transpose Rows to Columns ?
|
|
| bogtom@gmail.com 2006-03-29, 8:26 pm |
| I have records
ID Sku Name Date
2 41 Blair 01/04/03
3 45 John 03/04/03
that should look like...
ID 2 3
Sku 41 45
Name Blair John
.....
and so on. Number of source rows will be fixed (12) so no of target
columns will be 12 too.
Anyone know of a quick way to do this via simple SQL ?
| |
| Serge Rielau 2006-03-29, 8:26 pm |
| bogtom@gmail.com wrote:
> I have records
>
> ID Sku Name Date
> 2 41 Blair 01/04/03
> 3 45 John 03/04/03
>
> that should look like...
>
> ID 2 3
> Sku 41 45
> Name Blair John
> ....
>
> and so on. Number of source rows will be fixed (12) so no of target
> columns will be 12 too.
>
> Anyone know of a quick way to do this via simple SQL ?
>
This should work on all RDBMS:
CREATE TABLE Sales(Year INTEGER,
Quarter INTEGER,
Results INTEGER);
SELECT Year,
MAX(CASE WHEN Quarter = 1
THEN Results END) AS Q1,
MAX(CASE WHEN Quarter = 2
THEN Results END) AS Q2,
MAX(CASE WHEN Quarter = 3
THEN Results END) AS Q3,
MAX(CASE WHEN Quarter = 4
THEN Results END) AS Q4
FROM Sales
GROUP BY Year
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
| |
| jim_geissman@countrywide.com 2006-03-30, 3:25 am |
| I understand SQL 2005 T-SQL has a Pivot command. Would that do it?
| |
| Serge Rielau 2006-03-30, 3:25 am |
| jim_geissman@country
wide.com wrote:
> I understand SQL 2005 T-SQL has a Pivot command. Would that do it?
>
OP didn't state the version....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
|
|
|
|