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
 General SQL Server Forums
 New to SQL Server Programming
 how to repeat columns according to row data change
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/08/2013 :  07:16:33  Show Profile  Reply with Quote
Hello all,

I have table in which i need to show first column rows as columns

and rest of the columns max value
my input :

Companyname  Qty  Amount   tax
A             1      2      2
A             2      4      2
B             1      2      3
C             1      2      2
C             3      2      2


here i need to show like :

        A                  B                C
Qty  Amount   tax | Qty  Amount   tax  | Qty  Amount   tax
3       6      4     1       2     3      4      4      4


please suggest me

P.V.P.MOhan

Edited by - mohan123 on 02/08/2013 07:28:10

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/08/2013 :  07:51:16  Show Profile  Reply with Quote
and rest of the columns max value <-- But in your sample data you are summing them rather than taking max value. Regardless:
SELECT
	SUM(CASE WHEN Companyname = 'a' THEN qty ELSE 0 END) AS AQty,
	SUM(CASE WHEN Companyname = 'a' THEN Amount ELSE 0 END) AS AAmount,
	SUM(CASE WHEN Companyname = 'a' THEN tax ELSE 0 END) AS ATax,
	SUM(CASE WHEN Companyname = 'b' THEN qty ELSE 0 END) AS BQty,
	SUM(CASE WHEN Companyname = 'b' THEN Amount ELSE 0 END) AS AAmount,
	SUM(CASE WHEN Companyname = 'b' THEN tax ELSE 0 END) AS ATax,
	SUM(CASE WHEN Companyname = 'c' THEN qty ELSE 0 END) AS BQty,
	SUM(CASE WHEN Companyname = 'c' THEN Amount ELSE 0 END) AS AAmount,
	SUM(CASE WHEN Companyname = 'c' THEN tax ELSE 0 END) AS ATax
FROM
	Tbl
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/08/2013 :  07:58:40  Show Profile  Reply with Quote
it gave me exactly what i want is there any way to do it in using Pivot table ???

P.V.P.MOhan
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/08/2013 :  08:33:20  Show Profile  Reply with Quote
PIVOT operator in T-SQL allows only one pivot column, so if you do want to use the PIVOT operator, you would have to pivot them separately and then join the 3 sub-pivots somehow. Not sure that that is the most readable or efficient approach. For what it is worth, what I am thinking is something like this, but not necessarily recommending this:
SELECT * FROM
	(SELECT a AS AQty, b AS BQty, c AS CQty FROM
	(SELECT CompanyName,qty FROM Tbl ) s
	PIVOT(SUM(qty) FOR companyname IN ([a],[b],[c]))P1 )P1
	
	CROSS JOIN 
	
	(SELECT a AS AAmount, b AS BAmount, c AS CAmount FROM
	(SELECT CompanyName,amount FROM Tbl ) s
	PIVOT(SUM(amount) FOR companyname IN ([a],[b],[c]))P2 )P2

	CROSS JOIN 

	(SELECT a AS ATax, b AS BTax, c AS CTax FROM
	(SELECT CompanyName,tax FROM Tbl ) s
	PIVOT(SUM(tax) FOR companyname IN ([a],[b],[c]))P3 )P3
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/08/2013 :  08:40:22  Show Profile  Reply with Quote
it worked like a charm james thanks

P.V.P.MOhan
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/08/2013 :  11:08:47  Show Profile  Reply with Quote
You are very welcome - glad to help.
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.06 seconds. Powered By: Snitz Forums 2000