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 2012 Forums
 Transact-SQL (2012)
 help wit pivot table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 07/03/2013 :  04:16:54  Show Profile  Reply with Quote
I have a query that retursn

name, country ,count


but I want to show it in a table

that's

name on the left and country across the top

so it would be


program 1 program 2 program 3
name 1 1
name 2 5
name 3

what's the best way to do this

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/03/2013 :  04:33:42  Show Profile  Reply with Quote
use pivot

SELECT *
FROM (YourQueryHere) q
PIVOT (SUM(count) FOR country IN (CountryValue1,CountryValue2,...))p


Replace code in blue with actual content

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

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 07/03/2013 :  04:35:09  Show Profile  Reply with Quote
can i pull the countries from another query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/03/2013 :  04:40:37  Show Profile  Reply with Quote
you can but for passing them to above query you need dynamic sql



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

esthera
Flowing Fount of Yak Knowledge

1379 Posts

Posted - 07/03/2013 :  05:13:14  Show Profile  Reply with Quote
thanks for your help I did this
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/03/2013 :  05:28:01  Show Profile  Reply with Quote
welcome

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

SQLS33ker
Starting Member

USA
12 Posts

Posted - 07/11/2013 :  00:26:27  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

use pivot

SELECT *
FROM (YourQueryHere) q
PIVOT (SUM(count) FOR country IN (CountryValue1,CountryValue2,...))p


Replace code in blue with actual content

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Hi,

I'm also learning about pivot tables. I thought that the best way would be to first use a table expression and then pivot against it?

so something like...

with pivotdata as
(
select
name,
country,
count
from yourtblname
)
select
name, [1], [2], [3]
from pivotdata
pivot(sum(count) for country in ([1],[2],[3])) as P;

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/11/2013 :  01:01:59  Show Profile  Reply with Quote
they both are equivalent. you're using a CTE and I'm using a derived table instead. There will not much change in execution plan for both

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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