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 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

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. Here below you can find the script to create required objects for demo.

-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot

CREATE TABLE TblPivot
(
Seq int not null identity(1,1),
ItemCode int,
ItemModel varchar(25),
ItemName varchar(100),
ItemColour varchar(50)
)

GO

-- Inerting some sample records

INSERT INTO TblPivot
(
	ItemCode,
	ItemModel,
	ItemName,
	ItemColour
)
SELECT 1,'S1024','Samsung Mobile','Red'
UNION ALL
SELECT 2,'N1465','Nokia Mobile','Blue'
UNION ALL
SELECT 3,'N1689','Nokia Mobile','Green'
UNION ALL
SELECT 4,'M1642','Motorola Mobile','Red'
UNION ALL
SELECT 5,'S2358','Samsung Mobile','Green'
UNION ALL
SELECT 2,'N2376','Nokia Mobile','Blue'
UNION ALL
SELECT 1,'S3245','Samsung Mobile','Red'
UNION ALL
SELECT 2,'N3421','Nokia Mobile','Blue'

GO

These are the just sample records for the demo. Now here we are applying pivoting on multiple columns named ItemColour and ItemName. Following queries you can use for the same. So let’s run it and check result set.

SELECT
	Seq, 
	ItemCode,
	ItemModel, 
	ItemColour,
	ItemName
FROM TblPivot

-- Applying pivoting on multiple columns
SELECT
*
 FROM
 (
    SELECT 
	 Seq,
	 ItemCode,
	 ItemModel, 
	 ItemName, 
	 ItemColour
	FROM TblPivot
 ) AS P

 -- For ItemColour
PIVOT
(
  Count(ItemCode) FOR ItemColour IN ([Red], [Blue], [Green])
) AS pv1

-- For ItemName
PIVOT
(
  Count(ItemModel) FOR ItemName IN ([Samsung Mobile], [Nokia Mobile], [Motorola Mobile])
) AS pv2

GO

pivotingonmultiplecolumns

You can also implement the above script dynamically as previous post. Let me know your comments if you have something for pivoting.

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% · 7465
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
10
 
0
Lifesaver
 
1
Refreshed
 
 
0
Incorrect



Submit

5  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
    2765 · 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
    3071 · 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
    1010 · 0% · 26

Your Comment


Sign Up or Login to post a comment.

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