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

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

Apr 11 2012 12:00AM by Paresh Prajapati   

We have seen example of Changing Rows to Columns Using PIVOT. Hope you liked that post and you enjoyed a lot also. As we know that we need to hard-code the values for pivoting which we have did for color values. for the values updates in table or whenever values changed in the table for the column which we use in pivot then we have to update the script as per that otherwise it wont work or do not give correct information. Also I got same comment from user for the post which you  can reach from above link.

I am going to explain the same scenario and the resolution for the same, but you need to read original post properly. So requesting you to read it first if you did not read. So whenever we add new color values in table, the same pivot query does not return information about the new values. So let us add new value and prepare the query and run,

-- Inserting one more record in existing table created in earlier post
INSERT INTO TblPivot
(
	ItemCode,
	ItemName,
	ItemColour
)
SELECT 
	6,
	'Samsung Mobile',
	'Silver'

After adding above new value, the pivot query which used in earlier post does not return right result set. Here the solution for such type of issue we need to make the query with dynamic columns as following, We have get distinct values and embedded as comma separated using ‘[‘ and ‘]’. To make dynamic we can use COALESCE or FOR XML PATH.

DECLARE @ColourColumn varchar(200)
DECLARE @sql varchar(1000)

CREATE TABLE #Colours
(
Colour varchar(500)
)

INSERT INTO #Colours (Colour)
SELECT 
	DISTINCT '[' + ItemColour + ']' 
FROM TblPivot

-- Creating Column Names for Pivot
SELECT @ColourColumn = COALESCE(@ColourColumn + ',', '') +
Colour
FROM #Colours

/*
--OR
SET @ColourColumn = (SELECT  SUBSTRING(
	(SELECT DISTINCT ',' + Colour
		FROM #Colours
		FOR XML PATH('')),2,200000) 
	)
*/

DROP TABLE #Colours

SET @sql = 
'
	SELECT
	*
	 FROM
	 (
	 SELECT 
		 ItemCode, 
		 ItemName, 
		 ItemColour
	 FROM TblPivot
	 ) AS P
	PIVOT
	(
	  Count(ItemName) FOR ItemColour IN ('+@ColourColumn+')
	) AS pv
'
EXEC (@sql)

dynamic_Pivot

I want you to share your ideas for such issues which you faced and the resolutions for the same.

Tags: sql, ms sql, scripts, query, ms sql server, #SQL Server, #sql, database, sql server general, Pivot, Dynamic


Paresh Prajapati
6 · 23% · 7475
9
 
 
 
 
0
Incorrect



Submit

1  Comments  

  • Paresh, This is very helpful: simple and easy What would be server overheads on this. I plan using this for 10,000+ rows. SQL 2008. I do not want to overkill the server. Thanks,

    commented on May 24 2012 11:11AM
    raghu1
    1417 · 0% · 15

Your Comment


Sign Up or Login to post a comment.

"Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server" rated 5 out of 5 by 9 readers
Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]