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
 General SQL Server Forums
 New to SQL Server Programming
 how to repeat columns according to row data change

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-08 : 07:16:33
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 07:51:16
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

252 Posts

Posted - 2013-02-08 : 07:58:40
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 08:33:20
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

252 Posts

Posted - 2013-02-08 : 08:40:22
it worked like a charm james thanks

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 11:08:47
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -