help wit pivot table - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

help wit pivot table

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-07-03 : 04:16:54

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

52326 Posts

Posted - 2013-07-03 : 04:33:42

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-07-03 : 04:35:09

can i pull the countries from another query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 04:40:37

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-07-03 : 05:13:14

thanks for your help I did this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 05:28:01

welcome

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

SQLS33ker
Starting Member

16 Posts

Posted - 2013-07-11 : 00:26:27

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...
[code]
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 01:01:59

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources