SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Converting Columns into Rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Fortran77
Starting Member

United Kingdom
4 Posts

Posted - 03/21/2012 :  09:04:17  Show Profile  Reply with Quote
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
52325 Posts

Posted - 03/21/2012 :  10:26:19  Show Profile  Reply with Quote
not fully clear

can you explain how you got the row 1 2 3 3 3?

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

Go to Top of Page

Fortran77
Starting Member

United Kingdom
4 Posts

Posted - 03/21/2012 :  10:52:04  Show Profile  Reply with Quote
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.

Go to Top of Page

Fortran77
Starting Member

United Kingdom
4 Posts

Posted - 03/21/2012 :  10:53:47  Show Profile  Reply with Quote
2011/01, 2011/02, 2011/03 are the column headings for 200, 200, 100 in the second list!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/21/2012 :  12:13:10  Show Profile  Reply with Quote
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/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/22/2012 :  01:30:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Fortran77
Starting Member

United Kingdom
4 Posts

Posted - 03/22/2012 :  05:59:43  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/22/2012 :  16:03:52  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000