Pivot probs, how to convert data in row n column

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 2008 Forums

Transact-SQL (2008)

Pivot probs, how to convert data in row n column

Author

Topic

rohan_iiitc
Starting Member

11 Posts

Posted - 2010-05-06 : 03:49:16

PLease help me, i am trying but its not working.

Inputs
-----------------------------------------------------------------
CREATE TABLE TBL_SALEQTY
(
BranchCode varchar(20),
SaleType Varchar(20),
)
GO

INSERT INTO TBL_SALEQTY
(BranchCOde,SaleType)
SELECT 50101, 'SAL_T1' UNION ALL
SELECT 50101, 'SAL_T1' UNION ALL
SELECT 50101, 'SAL_T1' UNION ALL
SELECT 50101, 'SAL_T1' UNION ALL
SELECT 50101, 'SAL_T1' UNION ALL
SELECT 50101, 'SAL_T3' UNION ALL
SELECT 50101, 'SAL_T3' UNION ALL
SELECT 50101, 'SAL_T3' UNION ALL
SELECT 50101, 'SAL_T3' UNION ALL
SELECT 50101, 'SAL_T3' UNION ALL
SELECT 50101, 'SAL_T3' UNION ALL
SELECT 50101, 'SAL_T4' UNION ALL

SELECT 50102, 'SAL_T1' UNION ALL
SELECT 50102, 'SAL_T1' UNION ALL
SELECT 50102, 'SAL_T1' UNION ALL
SELECT 50102, 'SAL_T1' UNION ALL
SELECT 50102, 'SAL_T1' UNION ALL
SELECT 50102, 'SAL_T1' UNION ALL
SELECT 50102, 'SAL_T3' UNION ALL
SELECT 50102, 'SAL_T4' UNION ALL
SELECT 50102, 'SAL_T4' UNION ALL
SELECT 50102, 'SAL_T4' UNION ALL
SELECT 50102, 'SAL_T4' UNION ALL

SELECT 50103, 'SAL_T1' UNION ALL
SELECT 50103, 'SAL_T1' UNION ALL
SELECT 50103, 'SAL_T1' UNION ALL
SELECT 50103, 'SAL_T1' UNION ALL
SELECT 50103, 'SAL_T1' UNION ALL
SELECT 50103, 'SAL_T4'
GO

I Want output as below
-------------------------------------------------------------------

----------------------------------------------
| BRANCH | SAL_T1 | SAL_T2 | SAL_T3 | TOTAL |
----------------------------------------------
| 50101 | 03 | 02 | 06 | 12 |
----------------------------------------------
| 50102 | 04 | 02 | 01 | 11 |
----------------------------------------------
| 50103 | 00 | 05 | 00 | 06 |
----------------------------------------------

My Query
-------------------------------------------------------------------
1)
SELECT BranchCode,
SUM(CASE WHEN SaleType = 'SAL_T1' THEN COUNT(*) ELSE 0 END) AS [SALETYPE1],
SUM(CASE WHEN SaleType = 'SAL_T2' THEN COUNT(*) ELSE 0 END) AS [SALETYPE2],
SUM(CASE WHEN SaleType = 'SAL_T3' THEN COUNT(*) ELSE 0 END) AS [SALETYPE3],
SUM(SaleType) AS Total
FROM TBL_SALEQTY
GROUP BY BranchCode;

Error : Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

2)
SELECT BranchCode,
[SAL_T1] AS SALETYPE1,
[SAL_T2] AS SALETYPE2,
[SAL_T3] AS SALETYPE3
From
(SELECT BranchCode, SaleType FROM TBL_SALEQTY) AS PP
PIVOT
( COUNT(BranchCOde)
FOR ALertCOde IN ([SAL_T1],[SAL_T2], [SAL_T3])
) AS PVT
ORDER BY BranchCode;

Error : Msg 325, Level 15, State 1, Line 7
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 04:21:24

If you want to count sales_types per each BranchCode you can query
1)
SELECT BranchCode,
SUM(CASE WHEN SaleType = 'SAL_T1' THEN 1 ELSE 0 END) AS [SALETYPE1],
SUM(CASE WHEN SaleType = 'SAL_T2' THEN 1 ELSE 0 END) AS [SALETYPE2],
SUM(CASE WHEN SaleType = 'SAL_T3' THEN 1 ELSE 0 END) AS [SALETYPE3],
SUM(CASE WHEN SaleType IN ('SAL_T1','SAL_T2','SAL_T3') THEN 1 ELSE 0 END) AS Total
FROM TBL_SALEQTY
GROUP BY BranchCode;

2) What is compatibility level of your database? You can find it by query SELECT name, compatibility_level FROM sys.databases

apodemus

rohan_iiitc
Starting Member

11 Posts

Posted - 2010-05-06 : 04:27:47

Thanks apodemus, It worked

The compatibilty level is

name compatibility_level
-------------------------------
master 100
tempdb 100
model 100
msdb 100
ReportServer 100
ReportServerTempDB 100
SwiftDb 80
UBIRealtime 80
UBIHistory 90
History 80
Realtime 80

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 05:13:56

IF you need to use PIVOT on databases with compatibility level below 90 it won't work. I can't tell you if you can incrace comatibility level, that's yor databases :).

apodemus

rohan_iiitc
Starting Member

11 Posts

Posted - 2010-05-07 : 05:46:20

thanks apodemus

I wil change and check as its a test database. I would like to know , for which all params do i need to update.
should i make it 90 or 100.

please guide.

Thanks

apodemus
Starting Member

30 Posts

Posted - 2010-05-07 : 08:16:18

if your SQL Server is 2008 you can upgrade to 100

apodemus

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 09:16:56

This is for dynamic pivot
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources