FUN: Please show us how to use PIVOT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

FUN: Please show us how to use PIVOT

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-07-01 : 19:17:22

Let's have some fun.

Who can pivot this without using a cursor?
I have a resultset from my OPENXML() function such as this statement puts out:


declare @t as table (InstanceID Int, ColumnName Sysname, Datatype VARCHAR(20), Value VARCHAR(MAX));
insert into @t (InstanceID, ColumnName, Datatype, Value) values
  (1, 'Name', 'Varchar( 50)', 'George')
, (1, 'City', 'Varchar(100)', 'Bremerton')
, (1, 'Children', 'INT', '3')
, (20, 'Name', 'Varchar( 50)', 'Tom')
, (20, 'City', 'Varchar(100)', 'Bremerton')
, (20, 'Children', 'INT', '3')
, (33, 'Name', 'Varchar( 50)', 'Suzy')
, (33, 'City', 'Varchar(100)', 'Seattle')
, (33, 'Children', 'INT', '3')
, (44, 'Name', 'Varchar( 50)', 'Kris')
, (44, 'City', 'Varchar(100)', 'Silverdale')
, (44, 'Children', 'INT', '0')
, (52, 'Name', 'Varchar( 50)', 'Joe')
, (52, 'City', 'Varchar(100)', 'Boston')
, (52, 'Children', 'INT', '1')
 select * from @t

I need a result set such as this statement puts out:


	SELECT  CAST('George' AS Varchar( 50)) AS [Name]
	      , CAST('Bremerton' AS Varchar(100)) AS [City]
	      , CAST('3' AS INT) AS [Children]
	union all
	select  CAST('Tom' AS Varchar( 50)) AS [Name]
	      , CAST('Bremerton' AS Varchar(100)) AS [City]
	      , CAST('3' AS INT) AS [Children]
	union all
	select  CAST('Suzy' AS Varchar( 50)) AS [Name]
	      , CAST('Seattle' AS Varchar(100)) AS [City]
	      , CAST('3' AS INT) AS [Children]
	union all
	select  CAST('Kris' AS Varchar( 50)) AS [Name]
	      , CAST('Silverdale' AS Varchar(100)) AS [City]
	      , CAST('0' AS INT) AS [Children]
	union all
	select  CAST('Joe' AS Varchar( 50)) AS [Name]
	      , CAST('Boston' AS Varchar(100)) AS [City]
	      , CAST('1' AS INT) AS [Children]

I would prefer this be done by PIVOT or UNPIVOT, but I don't know how to do that. This doesn't have to be done by text manipulation, but here is an example of how you would if you had to.


DECLARE @STMT VARCHAR(MAX)
SET @STMT = 'SELECT '
SELECT @STMT = @STMT  
      + CASE VALUE 
        WHEN '' THEN ', NULL' 
        ELSE ', CAST(''' + VALUE + ''' AS ' +  DATATYPE + ')' 
	END 
      + ' AS [' + COLUMNNAME + ']' 		
FROM @T

SET @STMT = REPLACE(@STMT, 'SELECT ,' , 'SELECT ' )
EXEC MASTER..SP_SQLEXEC @STMT
GO

~ Shaun Merrill
Seattle area

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-02 : 04:56:48

[code]SELECT MAX(CASE WHEN ColumnName = 'Name' THEN Value ELSE NULL END) AS [Name],
MAX(CASE WHEN ColumnName = 'City' THEN Value ELSE NULL END) AS [City],
MAX(CASE WHEN ColumnName = 'Children' THEN Value ELSE NULL END) AS [Children]
FROM @T
GROUP BY InstanceID
ORDER BY InstanceID[/code]

N 56°04'39.26"
E 12°55'05.63"

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-07-02 : 14:03:08

Peso,
I remember now . . . I used to use that design pattern with SUM() instead of MAX() when making a pivot report.
I'm astonished at how well it works for this--I NEVER would have thought of it that way. The only thing I'm missing is the datatype, and I need to make it dynamic.


SELECT	CAST(MAX(CASE WHEN ColumnName = 'Name'	   THEN Value ELSE NULL END) AS VARCHAR( 50)) AS [Name],
	CAST(MAX(CASE WHEN ColumnName = 'City'	   THEN Value ELSE NULL END) AS VARCHAR(100)) AS [City],
	CAST(MAX(CASE WHEN ColumnName = 'Children' THEN Value ELSE NULL END) AS INT         ) AS [Children]
FROM		@T
GROUP BY	InstanceID

But this is much more efficient. In fact, it is exciting!

I don't know how it works, but it sure does! If I could understand this, it would raise my expertise to a new level.
Does anyone care to expound on the use of MAX() and GROUP BY in this way?
(I'm feeling a Soapbox-Opportunity coming on here!)

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-07-02 : 14:24:44

I think I am getting this... When we group by the InstanceID, then MAX() chooses the only record with that name.
MS Access has grouping functions called FIRST() and LAST() which SQL leaves out because MAX() and MIN() are functional replacements.

Can we make a new section of this forum which describes SQL design patterns in a Wiki? Let's have your opinion on what this design pattern should be named.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-02 : 17:15:41

The technique you're describing is basic cross-tabbing or pivoting. There are a few articles on SQLTeam about various techniques:

http://www.sqlteam.com/article/sql-server-crosstab-utility
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
http://weblogs.sqlteam.com/jeffs/articles/5120.aspx

Those are all for dynamically generating pivoted columns. If you have a fixed set of columns, the CASE technique you're using is fine, or the PIVOT function in SQL 2005 and higher can be used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources