| Author |
 Topic  |
|
|
Fortran77
Starting Member
United Kingdom
4 Posts |
 Posted - 03/21/2012 : 09:04:17
|
Hi,
I have a table which looks likes this when you use a select Statement:
1 2 3 4
CCAS ASPT2 200 2011/01
CCAS ASPT2 200 2011/02
CCAS ASPT2 100 2011/03
CCAS CODI 10 2011/01
CCAS CODI 10 2011/03
DEL DIAI2 25 2011/01
DEL DIAI2 10 2011/02
DEL DIAI2 35 2011/03
DEL DIAI3 20 2011/01
DEL DIAI3 35 2011/02
I would like to be able to manipulate the table(using T-SQL) so that the final outut looks likes this:
2011/01 2011/02 2011/03
1 2 3 3 3
CCAS ASPT2 200 200 100
CCAS CODI 10 0 10
DEL DIAI2 25 10 35
DEL DIAI3 20 35 0
That is to tranpose some columns to rows with the corresponding values in each row.
That is to have the YYYY/MM as column headdings with the correspong values from the first three rows written as one row.
I hope this is clear!
Any idea how to do this?
Best regards,
Fortran77
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 03/21/2012 : 10:26:19
|
not fully clear
can you explain how you got the row 1 2 3 3 3?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Fortran77
Starting Member
United Kingdom
4 Posts |
Posted - 03/21/2012 : 10:52:04
|
Hi,
This should read like this;
CCAS ASPT2 200 2011/01
CCAS ASPT2 200 2011/02
CCAS ASPT2 100 2011/03
CCAS CODI 10 2011/01
CCAS CODI 10 2011/03
DEL DIAI2 25 2011/01
DEL DIAI2 10 2011/02
DEL DIAI2 35 2011/03
DEL DIAI3 20 2011/01
DEL DIAI3 35 2011/02
will tranlate to:
2011/01 2011/02 2011/03
CCAS ASPT2 200 200 100
CCAS CODI 10 0 10
DEL DIAI2 25 10 35
DEL DIAI3 20 35 0
I hope this clear now.
|
|
|
|
Fortran77
Starting Member
United Kingdom
4 Posts |
Posted - 03/21/2012 : 10:53:47
|
| 2011/01, 2011/02, 2011/03 are the column headings for 200, 200, 100 in the second list! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 03/21/2012 : 12:13:10
|
use something like
SELECT [1],[2],
MAX(CASE WHEN [4] = '2011/01' THEN [3] END) AS [2011/01],
MAX(CASE WHEN [4] = '2011/02' THEN [3] END) AS [2011/02],
MAX(CASE WHEN [4] = '2011/03' THEN [3] END) AS [2011/03]
FROM table
GROUP BY [1],[2]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
Fortran77
Starting Member
United Kingdom
4 Posts |
Posted - 03/22/2012 : 05:59:43
|
quote:
Originally posted by visakh16
use something like
SELECT [1],[2],
MAX(CASE WHEN [4] = '2011/01' THEN [3] END) AS [2011/01],
MAX(CASE WHEN [4] = '2011/02' THEN [3] END) AS [2011/02],
MAX(CASE WHEN [4] = '2011/03' THEN [3] END) AS [2011/03]
FROM table
GROUP BY [1],[2]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Visakh16.... You are star! That is exactly what I wanted.
Many many thanks for this useful hint and the SELECT staement.
Deeply appreciated.
Fortran77 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 03/22/2012 : 16:03:52
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Converting Columns into Rows
