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 GA GB startdate enddate

Pavan 2 6 1/1/2015 1/1/2015

Hema 5 6 1/1/2015 1/1/2015

Surya 5 0 1/1/2015 1/1/2015

Pavan 8 1 1/2/2015 1/8/2015

Hema 3 1 1/2/2015 1/8/2015

Surya 1 2 1/2/2015 1/8/2015

Pavan 10 4 1/9/2015 1/15/2015

Hema 3 0 1/9/2015 1/15/2015

Surya 6 13 1/9/2015 1/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: 1000539 More actions · Answer 1

See the following article:

http://qa.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)
Intro to Tally Tables and Functions

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

manub22

Hall of Fame

Points: 3133

March 12, 2015 at 6:19 am

#1783552

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:

startdate Hema Pavan Surya

2015-01-01 NULL NULL 5

2015-01-09 3 NULL NULL

2015-01-02 3 8 NULL

2015-01-02 NULL NULL 1

2015-01-09 NULL 10 NULL

2015-01-01 5 2 NULL

2015-01-09 NULL NULL 6

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: 183686 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

suresh0534

SSCommitted

Points: 1966

March 12, 2015 at 2:03 pm

#1783716

Thank you so much Luis Cazares,

You are Awesome!!!.

I learned cross Apply now how to use...

Thanks,