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
 pivot

Author  Topic 

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-06-13 : 05:31:13
i have a data as below:

ID Name Amount Country Payment
10 Mary 123.45 US NULL
10 Mary 999.99 AU 70
10 John 123.45 US NULL
10 John 999.99 AU 70
10 Zack 123.45 US NULL
10 Zack 999.99 AU 70

how can i output as below:

ID Name Amount Country Payment
10 Mary 123.45 AU 70
10 John 123.45 AU 70
10 Zack 123.45 AU 70

use pivot?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 05:35:35
SELECT *
FROM Table
WHERE Payment IS NOT NULL

--Is there any logic behind that output?
why PIVOT? for that above output pivot is not required
--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:36:46
[code]
SELECT ID,Name,
MAX(CASE WHEN Country='US' THEN Amount END) AS Amount,
MAX(CASE WHEN Country='AU' THEN Country END) AS Country,
MAX(Payment) AS Payment
FROM Table
GROUP BY ID,Name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:37:47
I cant understand the significance of the output though. whats the purpose of merging US Amount to AU?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -