pivot? - 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 2005 Forums

Transact-SQL (2005)

pivot?

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-01 : 12:22:14

Can anyone point me in the direction of how to do a pivot with this...?

I have a table that has

organization
language

i want to be able to count the amount of people that speak a certain language, if that organizaton has no speakers of a certain language then it should show 0.

Spanish | German | English
---------------------------------------
organization1 0 | 5 | 2
---------------------------------------
organization2 2 | 1 | 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:24:40

[code]SELECT organisation,Spanish, German , English
FROM Table t
PIVOT (COUNT(PKCol) FOR language IN ([Spanish], [German] , [English]))p
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:25:51

and if you need this to happen dynamically

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-02-01 : 12:49:11

[code]
drop table #org_lan
create table #org_lan(orgname char(8), lang char(8), People int)
insert into #org_lan

SELECT 'MS', 'Zulu', 1
UNION ALL
SELECT 'MS', 'Punjabi', 12
UNION ALL
SELECT 'MS', 'Spanish', 13
UNION ALL
SELECT 'MS', 'Zulu', 1
UNION ALL
SELECT 'MS', 'English', 30
UNION ALL
SELECT 'My Org', 'English', 30

DECLARE @listCol VarChar(2000)
DECLARE @query VarChar(max)
SELECT @listCol = Stuff((SELECT DISTINCT
'],[' + lang
FROM #org_lan
FOR XML PATH('')
), 1, 2, '') + ']'

SET @query=
'SELECT *
FROM #org_lan
PIVOT (SUM(People) FOR lang IN ('+@listCol+'))p'

print @query
EXECUTE (@Query)[code]

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:52:02

quote:
Originally posted by yosiasz

[code]
drop table #org_lan
create table #org_lan(orgname char(8), lang char(8), People int)
insert into #org_lan

SELECT 'MS', 'Zulu', 1
UNION ALL
SELECT 'MS', 'Punjabi', 12
UNION ALL
SELECT 'MS', 'Spanish', 13
UNION ALL
SELECT 'MS', 'Zulu', 1
UNION ALL
SELECT 'MS', 'English', 30
UNION ALL
SELECT 'My Org', 'English', 30

DECLARE @listCol VarChar(2000)
DECLARE @query VarChar(max)
SELECT @listCol = Stuff((SELECT DISTINCT
'],[' + lang
FROM #org_lan
FOR XML PATH('')
), 1, 2, '') + ']'

SET @query=
'SELECT *
FROM #org_lan
PIVOT (SUM(People) FOR lang IN ('+@listCol+'))p'

print @query
EXECUTE (@Query)[code]

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

this is same as what is given in link i posted

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-02-01 : 13:02:20

oops, sorry, did not see your post there visakh before I click on submit button

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 13:05:22

no probs

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-02 : 01:49:49

visakh16,

that worked perfect, and I feel silly for asking such a simple question.

My problem is bigger than I though though, it seems that in the organizations table there are 2 id's, organizationid and parentorganization. I need to be able to roll up all the child organization counts into the parent... All the organizations are the children of organizationid 1, and from there is branches off to 23 children and those children have children and some of those do as well...

Any ideas on how to do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 04:08:15

quote:
Originally posted by HalaszJ

visakh16,

that worked perfect, and I feel silly for asking such a simple question.

My problem is bigger than I though though, it seems that in the organizations table there are 2 id's, organizationid and parentorganization. I need to be able to roll up all the child organization counts into the parent... All the organizations are the children of organizationid 1, and from there is branches off to 23 children and those children have children and some of those do as well...

Any ideas on how to do that?

sounds like what you need to use is recursive cte

http://www.sqlservercurry.com/2009/06/simple-family-tree-query-using.html

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-02 : 08:36:10

that example confused me more than I was...

do you know of another one that will work better for what I posted?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 09:33:29

quote:
Originally posted by HalaszJ

that example confused me more than I was...

do you know of another one that will work better for what I posted?

here's a link with explanation

http://msdn.microsoft.com/en-us/library/ms186243.aspx

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-02 : 11:35:56

I dont think this will work for me, my organization is a uniqueidentifier, not an int. I tried several different ways and all i get is the top level that has a null parent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 11:52:09

quote:
Originally posted by HalaszJ

I dont think this will work for me, my organization is a uniqueidentifier, not an int. I tried several different ways and all i get is the top level that has a null parent.

even then cant you join based on that?

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-02 : 12:17:02

ok i got it to show a hierarchy but it is showing the levels

level | organization | organizationid | organizationparent
----------------------------------------------------------
1 | org1 | guid1 | null
----------------------------------------------------------
2 | org2 | guid2 | guid1
----------------------------------------------------------
3 | org3 | guid3 | guid2
----------------------------------------------------------

i dont see how i roll all of the level 2 items into just 1 item... I want all the 3 and 4 level items to roll up to the second level so i can do my pivot there. I dont see how I can do a pivot with this since it is showing all the children rather than 1 common id i can group by.

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-03 : 02:44:39

anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 03:32:15

quote:
Originally posted by HalaszJ

ok i got it to show a hierarchy but it is showing the levels

level | organization | organizationid | organizationparent
----------------------------------------------------------
1 | org1 | guid1 | null
----------------------------------------------------------
2 | org2 | guid2 | guid1
----------------------------------------------------------
3 | org3 | guid3 | guid2
----------------------------------------------------------

i dont see how i roll all of the level 2 items into just 1 item... I want all the 3 and 4 level items to roll up to the second level so i can do my pivot there. I dont see how I can do a pivot with this since it is showing all the children rather than 1 common id i can group by.

can you explain what you mean by roll up levels to higher level with illlustration?

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-03 : 04:31:14

I basically have 24 organizations that I want to know how many people they have that speak a specific language.

In those 24 organizations, they have child organizations, 245 total

Org1
Child 1
Child 2
Child 3
Grand Child 1

The result should be

Child 1 | 5
Child 2 | 3
Child 3 | 20

Child 3 would have the total number from Child 3, and Grand Child 1 rolled up into the cont for Child 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 06:23:40

quote:
Originally posted by HalaszJ

I basically have 24 organizations that I want to know how many people they have that speak a specific language.

In those 24 organizations, they have child organizations, 245 total

Org1
Child 1
Child 2
Child 3
Grand Child 1

The result should be

Child 1 | 5
Child 2 | 3
Child 3 | 20

Child 3 would have the total number from Child 3, and Grand Child 1 rolled up into the cont for Child 3

thats doable. in your recursive part you can keep on adding count to a field which gets accumulated all the way down from initial level

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-02-03 : 07:19:58

i dont want to see the count of the children, i want to see the count of all the parents with their childrens numbers rolled in.

is there a sample somewhere that is simular to what I am looking for? I am completely lost this time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources