How to Write Pivot Statement

Question

Post reply

How to Write Pivot Statement

suresh0534

SSCommitted

Points: 1966
More actions
March 11, 2015 at 4:08 pm

#295128

Hi,
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME
)
INSERT INTO #mytable
(Name, GA,GB,startdate,enddate)
SELECT 'Pavan',2,6,'1/1/2015','1/1/2015' UNION ALL
SELECT 'Hema',5,6,'1/1/2015','1/1/2015' UNION ALL
SELECT 'Surya',5,0,'1/1/2015','1/1/2015' UNION ALL
SELECT 'Pavan',8,1,'1/2/2015','1/8/2015' UNION ALL
SELECT 'Hema',3,1,'1/2/2015','1/8/2015' UNION ALL
SELECT 'Surya',1,2,'1/2/2015','1/8/2015' UNION ALL
SELECT 'Pavan',10,4,'1/9/2015','1/15/2015' UNION ALL
SELECT 'Hema',3,0,'1/9/2015','1/15/2015' UNION ALL
SELECT 'Surya',6,13,'1/9/2015','1/15/2015' UNION ALL
Below is Our Sample Table Data.
Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015
how to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

See the following article:

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

manub22 Hall of Fame Points: 3163 More actions · Answer 2

Check the following article on PIVOT operator which mentions an example that matches with your scenario.

Link: http://sqlwithmanoj.com/2009/04/12/ms-sql-server-2005-new-feature-pivot-and-unpivot/[/url]

Here you will also see how to use UNPIVOT operator.

~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

suresh0534 SSCommitted Points: 1966 More actions · Answer 3

Thanks for your reply Jeff,

I used the below script , but it's not working like my ouput:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column

SELECT @ColumnName= ISNULL(@ColumnName + ',','')

+ QUOTENAME(Name)

FROM (SELECT DISTINCT Name FROM #mytable) AS UserNames

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

N'SELECT startdate, ' + @ColumnName + '

FROM #mytable

PIVOT(SUM(GA)

FOR Name IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery

print @ColumnName

When i run the above statement below is my output:

startdateHemaPavanSurya

2015-01-01 NULLNULL5

2015-01-09 3NULLNULL

2015-01-02 38NULL

2015-01-02 NULLNULL1

2015-01-09 NULL10NULL

2015-01-01 52NULL

2015-01-09 NULLNULL6

But my output should like , can we get the out put like below:

1/1/2015 Pavan Hema Surya SumTotal

Total 8 11 5 24

GA 2 5 5 12

GB 6 6 0 12

1/8/2015 Pavan Hema Surya SumTotal

Total 9 4 3 16

GA 8 3 1 12

GB 1 1 2 4

1/15/2015 Pavan Hema Surya SumTotal

Total 14 3 19 36

GA 10 3 6 19

GB 4 0 13 17

Thanks,

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You're describing 3 datasets, which is a terrible idea as you'll have problems managing them.

Instead, you need a single dataset that can be formatted in the presentation layer which I assume is a report.

Here's an option that unpivots the GA and GB columns adding a Total and then creates the dynamic pivot.

CREATE TABLE #mytable

(

Name varchar(50),

GA int,

GB int,

startdate DATETIME,

enddate DATETIME

)

INSERT INTO #mytable

(Name, GA,GB,startdate,enddate)

SELECT 'Pavan',2,6,'1/1/2015','1/1/2015' UNION ALL

SELECT 'Hema',5,6,'1/1/2015','1/1/2015' UNION ALL

SELECT 'Surya',5,0,'1/1/2015','1/1/2015' UNION ALL

SELECT 'Pavan',8,1,'1/2/2015','1/8/2015' UNION ALL

SELECT 'Hema',3,1,'1/2/2015','1/8/2015' UNION ALL

SELECT 'Surya',1,2,'1/2/2015','1/8/2015' UNION ALL

SELECT 'Pavan',10,4,'1/9/2015','1/15/2015' UNION ALL

SELECT 'Hema',3,0,'1/9/2015','1/15/2015' UNION ALL

SELECT 'Surya',6,13,'1/9/2015','1/15/2015'

DECLARE @SQL varchar(8000);

WITH cteNames AS(

SELECT DISTINCT Name

FROM #mytable

)

SELECT @SQL = '

SELECT enddate,

LTRIM(Description) Description, ' + CHAR(13) +

(SELECT ' SUM( CASE WHEN Name = ''' + Name

+ ''' THEN Value ELSE 0 END) AS ' + Name + ',' + CHAR(13)

FROM cteNames

FOR XML PATH(''),TYPE).value('.','varchar(max)')

+ ' SUM( Value) AS SumTotal

FROM #mytable

CROSS APPLY (VALUES(1, GA+GB, '' Total''),

(2, GA, ''GA''),

(3, GB, ''GB'')) oa(RowOrder, Value, Description)

GROUP BY enddate,

Description,

RowOrder

ORDER BY enddate,

RowOrder';

PRINT @SQL;

EXECUTE ( @SQL);

GO

DROP TABLE #mytable

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

suresh0534 SSCommitted Points: 1966 More actions · Answer 5

Thank you so much Luis Cazares,

You are Awesome!!!.

I learned cross Apply now how to use...

Thanks,