Converting Columns into Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting Columns into Rows

Author

Topic

Fortran77
Starting Member

4 Posts

Posted - 2012-03-21 : 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

52326 Posts

Posted - 2012-03-21 : 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

4 Posts

Posted - 2012-03-21 : 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

4 Posts

Posted - 2012-03-21 : 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

52326 Posts

Posted - 2012-03-21 : 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

22864 Posts

Posted - 2012-03-22 : 01:30:13

For dynamic PIVOT, use this
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Fortran77
Starting Member

4 Posts

Posted - 2012-03-22 : 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

52326 Posts

Posted - 2012-03-22 : 16:03:52

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources