Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 124
sql 123
ms sql server 118
ms sql 117
database 108
tsql 80
#SQL Server 78
t-sql 74
#sql 71
sql server general 66

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

Pivoting on multiple columns - SQL Server

Apr 18 2012 12:00AM by Paresh Prajapati   

Last time I have wrote about pivoting and the next after that post describes for dynamic columns which used for pivoting.  We have seen the example and the same example implemented with dynamic columns. You can read both posts which are following,

1. SQL Server - Changing Rows to Columns Using PIVOT

2. Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server

In earlier post I have applied pivoting on one column name ItemColour but here I would like to introduce pivoting on more than one columns. So let us jump on example and implement it for multiple columns. Continue Reading...

Tags: sql, sql server, ms sql, ms sql server, #SQL Server, #sql, database, sql server general, SQL Scripts, Pivot, Query, Dynamic,


Paresh Prajapati
6 · 23% · 7533
1 Readers said this post helped them to refresh memory
Nakul Vachhrajani said this post helped them to refresh memory on 4/19/2012 11:17:00 AM
Profile · Blog · Facebook · Twitter
11
 
0
Lifesaver
 
1
Refreshed
 
 
0
Incorrect



Submit

8  Comments  

  • HI, THANK YOU FOR YOU GOOD WEB SITE. IVE LOOKED AT IT, AND WROTE IT AS SHOWN, BUT THIS IS DOESNT WORK (((( , WHAT IS A PROBLEM, AND PLEASE HELP ME TO CHANGE IN PIVOT NULL TO ZERO

    SELECT * FROM (SELECT ACCOUNTNUMBER, BRANCHCODE, COMPONENT, BALANCE, GLCODE FROM FCJLIVE.CLTBACCOUNTCOMPBAL_BREAKUP)

    PIVOT (SUM(BALANCE) FOR COMPONENT IN ('PRINCIPAL', 'MAININT', 'AQDINT')) PV2 PIVOT (MAX(GLCODE) FOR COMPONENT IN ('PRINCIPAL', 'MAININT')) AS PV2

    commented on Jan 24 2013 10:28AM
    Elmi
    2806 · 0% · 4
  • Hi Paresh, I have the following problem : I have 2 tables, T1 and T2 T1

    Question                        QuestionID
    How are you 15
    What is your name   16
    How old are you 17
    City                        18
    Country                     19
    

    and T2

    Response           QuestionID
    fine           15
    John           16
    39         17
    New York           18
    U.S        19
    

    I want to create an TempTable and alter it dinamically with columns 'Question' and insert data 'Response' like

    How are you What is your name	How old are you	City	      Country
    fine                        John	                    39	                    New York	      U.S
    

    Can you let me know how can i achieve this? Thanks a lot. Hicham.

    commented on Mar 15 2013 11:10AM
    hicham4
    3126 · 0% · 2
  • The solution provided really isn't appropriate for a pivot. It's more like a column expansion, like this:

    SELECT  [p].[seq],
        	CASE WHEN [p].[ItemColour]	= 'Red'				THEN 1 ELSE 0 END as [Red], 
        	CASE WHEN [p].[ItemColour]	= 'Blue'			THEN 1 ELSE 0 END as [Blue], 
        	CASE WHEN [p].[ItemColour]	= 'Green'			THEN 1 ELSE 0 END as [Green], 
        	CASE WHEN [p].[ItemName]	= 'Samsung Moble'	THEN 1 ELSE 0 END as [Samsung Mobile],
        	CASE WHEN [p].[ItemName]	= 'Nokia Moble'		THEN 1 ELSE 0 END as [Nokia Mobile],
        	CASE WHEN [p].[ItemName]	= 'Motorola Mobile'	THEN 1 ELSE 0 END as [Motorola Mobile]
    FROM    @Pivot [p]
    

    If you want to pivot on multiple columns on a meaningful way, you have to combine the columns:

    SELECT * 
    FROM 
        (	SELECT	[p].[ItemCode], 
        			([p].[ItemName]) + '-' + ([p].[ItemColour]) as [desc]
        	FROM	@Pivot [p]
        ) [base]
        PIVOT
        	(	COUNT([ItemCode])
        		FOR [desc] IN 
        			(
        				[Samsung Mobile-Red],	[Samsung Mobile-Green],	[Samsung Mobile-Blue], 
        				[Nokia Mobile-Red],		[Nokia Mobile-Green],	[Nokia Mobile-Blue],
        				[Motorola Mobile-Red],	[Motorola Mobile-Green],	[Motorola Mobile-Blue]
        			)
        	) [pvt]
    

    This results in the more meaningful result:

    Samsung Mobile-Red Samsung Mobile-Green Samsung Mobile-Blue Nokia Mobile-Red Nokia Mobile-Green Nokia Mobile-Blue Motorola Mobile-Red Motorola Mobile-Green Motorola Mobile-Blue
    ------------------ -------------------- ------------------- ---------------- ------------------ ----------------- ------------------- --------------------- --------------------
    2                  1                    0                   0                1                  3                 1                   0                     0
    

    Another option is to combine the use of PIVOT for the CUBE operator:

    SELECT 
        [ItemName], 
        [All], 
        ISNULL([Red], 0) as [Red], 
        ISNULL([Blue], 0) as [Blue],  
        ISNULL([Green], 0) as [Green]
    FROM
        (
        	SELECT		ISNULL([p].[ItemName], 'All')	as [ItemName], 
        				ISNULL([p].[ItemColour], 'All')	as [ItemColor], 
        				COUNT(*)						as [cnt]
        	FROM		@Pivot [p]
        	GROUP BY	[p].[ItemName], [p].[ItemColour]
        	WITH CUBE
        ) [base]
        PIVOT
        (
        	SUM([cnt])
        	FOR [ItemColor] IN ([All], [Red], [Blue], [Green])
        ) pvt
    

    Which returns:

    ItemName             All         Red         Blue        Green
    -------------------- ----------- ----------- ----------- -----------
    All                  8           3           3           2
    Motorola Mobile      1           1           0           0
    Nokia Mobile         4           0           3           1
    Samsung Mobile       3           2           0           1
    
    commented on Mar 17 2013 10:54AM
    Marc Jellinek
    95 · 2% · 586
  • @hicham4

    Given that you will have a large number of respondents and large number of questions, it's probably a better idea to perform the pivot in a reporting tool like SQL Server Reporting Services (using a Tablix), Access (using a CrossTab query) or Excel (using a PivotTable).

    commented on Mar 17 2013 10:56AM
    Marc Jellinek
    95 · 2% · 586
  • nice... pivot using multiple column is new for me. nice concept.

    commented on Mar 21 2013 6:03AM
    riks
    1019 · 0% · 26
  • good contribution.

    I have a question, if the header is the same for two columns can work with PIVOT function, example: Enero2014Debit, Enero2014Credit, Thanks

    commented on Nov 5 2014 5:01PM
    mcardenas
    3126 · 0% · 2
  • Hello, any ideas on why I get an error on this query?

    Select * From ( select jobroute.wc As WC, dbo.MidnightOf(jrtsch.startdate) As StartDate, jobroute.job + cast(jobroute.suffix as nvarchar) as Job from jobroute inner join job on jobroute.job = job.job and jobroute.suffix = job.suffix inner join jrtsch on jobroute.job = jrtsch.job and jobroute.suffix = jrtsch.suffix and jobroute.opernum = jrtsch.opernum where job.type = 'J' and job.stat in ('F', 'R') and jrtsch.startdate is not null ) as P PIVOT ( count(Job) for StartDate IN ('06/25/2014', '07/03/2014','07/15/2014','07/16/2014','07/17/2014','07/18/2014','07/21/2014','07/22/2014','07/23/2014','09/01/2014','09/02/2014','09/03/2014','09/04/2014','09/05/2014','09/08/2014','09/09/2014','10/03/2014','10/06/2014','10/07/2014','12/22/2014','12/23/2014','12/24/2014','12/25/2014','12/26/2014','12/28/2014','12/29/2014','12/30/2014') )as pivottable order by startdate ;

    The error happens right after the "IN" statement.

    Thanks

    commented on Dec 22 2014 9:37PM
    vairam
    3126 · 0% · 2
  • Hello Vairam,

    Can you change '06/25/2014', '07/03/2014'... to [06/25/2014], [07/03/2014].. like in a query you provided and try again?

    commented on Jan 8 2015 6:55AM
    Paresh Prajapati
    6 · 23% · 7533

Your Comment


Sign Up or Login to post a comment.

"Pivoting on multiple columns - SQL Server" rated 5 out of 5 by 11 readers
Pivoting on multiple columns - SQL Server , 5.0 out of 5 based on 11 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]