SQL 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 2012 Forums

Transact-SQL (2012)

SQL Pivot

Author

Topic

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 04:38:21

Hi,
I have not used the PIVOT function before and need some help:

My data is like this:
CustID CustType CustValue
1 Type1 10
2 Type2 1
3 Type2 99
4 Type1 5

Could you assist me in using pivot to give me

CustType CustCount CustValue(Sum)
Type1 2 15
Type2 2 100

A simple example I hope will assist me in working further with this.

Much appreciated

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 04:49:33

SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CustValue) AS CustValue
FROM TableName
GROUP BY CustType

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 04:51:38

Refer these links for understanding PIVOT
http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/
For dynamic PIVOT:
http://dotnetgalactics.wordpress.com/2009/10/23/using-sql-server-20052008-pivot-on-unknown-number-of-columns-dynamic-pivot/

--
Chandu

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 04:54:35

OK thanks,

I left out a bit:

Each customer is assigned to an area, area 1, area 2, area 3 etc., and it is by area I need to do this aggregation, hence why I thought of PIVOT.

So by area, what count of customer types and sum of customer values.

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 05:01:07

Is this?

SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CustValue) AS CustValue
FROM TableName
GROUP BY CustType, area

If NOT, post the sample data and expected output
--
Chandu

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 05:08:28

The other issue is that CustValue is nvarchar type, as some records contain "REMOVED"

Can we also incorporate a CAST or CONVERT?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 05:25:59

quote:
Originally posted by mikesoper

The other issue is that CustValue is nvarchar type, as some records contain "REMOVED"

Can we also incorporate a CAST or CONVERT?

Thanks

then convert them to NULL first to ignore them from sum calculation.
something like


SELECT CustType , COUNT(CustID ) AS CustCount , SUM(NULLIF(CustValue,'REMOVED')) AS CustValue
FROM TableName
GROUP BY CustType

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

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 10:05:13

I get "Operand data type nvarchar is invalid for sum...."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 13:18:58

quote:
Originally posted by mikesoper

I get "Operand data type nvarchar is invalid for sum...."

CAST it to int then

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 23:11:00

SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CAST(NULLIF(CustValue,'REMOVED') AS INT)) AS CustValue
FROM TableName
GROUP BY CustType

--
Chandu

mikesoper
Starting Member

11 Posts

Posted - 2013-11-26 : 08:36:57

Hi,
Thanks this seems to be working. One of the reasons I mentionned a Pivot was because I also need to calculate the % of totals for each group, for example:

CustID CustType CustValue % of total
1 Type1 10 8%
2 Type2 1 1%
3 Type2 99 87%
4 Type1 5 4%

How could I acheive this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 09:28:50

[code]
SELECT CustType ,CustCount ,CustValue,
CustValue/SUM(CustValue) OVER () AS [% Of Total]
FROM
(
SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CAST(NULLIF(CustValue,'REMOVED') AS INT)) AS CustValue
FROM TableName
GROUP BY CustType
)t
[/code]

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

mikesoper
Starting Member

11 Posts

Posted - 2013-11-26 : 10:09:29

Thanks - I have tried this but the results for [% Of Total] are always 0?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 04:55:24

what about now?


SELECT CustType ,CustCount ,CustValue,
CustValue*100.0/SUM(CustValue) OVER () AS [% Of Total]
FROM
(
SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CAST(NULLIF(CustValue,'REMOVED') AS INT)) AS CustValue
FROM TableName
GROUP BY CustType
)t

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

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:05:35

Brilliant - can we round to 1 decimal point here as well?

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:10:04

Worked that last one out myself - thank you!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:13:14

cool. you're welcome

just FYI this was the reason for the 0 values

http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:16:21

Can we round up or down here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:21:05

quote:
Originally posted by mikesoper

Can we round up or down here?

round to how many decinmal places?

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

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:28:33

1 decimal place

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:30:41

quote:
Originally posted by mikesoper

1 decimal place

use ROUND fucntion for that

http://technet.microsoft.com/en-us/library/ms175003.aspx

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources