Pivoting data dynamically
-
Hi,
I have the following data:
- User | Element | Value
- ------|----------|--------
- User1 | el1 | 2.5
- User1 | el2 | 3.2
- User2 | el1 | 4.1
- User2 | el3 | 1.3
And I want it to look like this:
- User | el1 | el2 | el3
- ------|-----|------|-----
- User1 | 2.5 | 3.2 | NULL
- User2 | 4.1 | NULL | 1.3
Classic pivot operation right? Well yes, except that we're constantly going to be getting new element values and I want to be able to do this (semi-)dynamically. i.e. I don't want to have to build a new view from scratch every time I want the data displaying slightly differently. We've kicked around an idea of having a metadata table that lists all the elements which we can this reference inside a function that returns the pivoted data. Something like that anyway.
I'm interested to know how other people have gone about solving this problem.
Thanks
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
The approach (for the moment
) is to generate the fixed part of the query in a static variable and add the variable part of it from the metadata or a temp table.declare @STR as varchar(8000)
set @STR = 'select user '
select @STR = @STR + ' , coalesce(min(case when Element = '' + MetadataCol + ''' then Element end),'''') as ' + MetadataCol
from MetadataTable -- This could be built as a temp table (you save time if is already compiled
Set @STR = @STR + ' From RealTable Where ...'
exec(@str) -- use various @STR concat if length is big
in 2005 you will have (hopefully
) the glorious PIVOT and UNPIVOT commandsCheers!
* Noel -
Superb, thank you Noel.
I'm having a few fun and games with my string exceeding 8000 chars but I'm working on that. The Yukon windowing functions would help but of course if I had Yukon I could just use the pivot function which would mean I wouldn't need the windowing functions (probably)
Thanks again.
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Basically the same problem has been discussed in this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=205972
-
Jamie,
This limit is fairly common on large crosstabs there are some tricks to be used in the process in case the grow very big:
1. Several @STR variables (estimating limits of real values) for most occasions 8 is been my lucky number
2. Some people create temporary tables and perform the pivoting using the insert command ability to "slide" the column values (by only specifying one column and one value) -- This is slower but is very robust in the sence that you could deal with a very large number of columns (+700) without worring about string sizes
3. Some others create views with single character column names to minimize string length and number of string variables leaving the job to the presentation layer -- Eventhough it is fast it is very cumbersome to maintain
4. Last but not list this type of string generation can very efficiently done clientside given you "know" the pivot values and you will send the monster text in one shot (or two
). This tend to be the most practical , secure and flexible method but in not all occations you have access to client side App code (design)hth
* Noel -
Hi Jamie,
I have created a stored procedure for such task. I hope it will prove useful to you. Also, I have 3 versions of this type of stored procedure, each version being more complicated and more powerful than the first. This one is the simplest version I created.
*************** START of stored procedure ********************
CREATE PROCEDURE sp_transpose
/* This is a utility stored procedure to mimic the _GENXTAB command of FoxPro */
/* This SP generates cross-tab output similar to a pivot table */
@strSelectFields varchar(100), -- should be a string like field1,field2......such as TER,REGNCD, SREGCD, SREGNAMET, POR_OPZNCD, OZNAME
@strGroupByFields varchar(100), -- should be a string like field1,field2......such as TER,REGNCD, SREGCD, SREGNAMET, POR_OPZNCD, OZNAME
@strTranposeField varchar(100), -- field that would be seperate such as BOUND
@strCalcuField varchar(20), -- field that would be calculate such as TTLRQ
@strOtherCalcuString varchar(100), -- such as sum(IMB) as IMB
@strWhereCriteria varchar(300), -- such as where field1 > xx and field < yy
@strSourceTableName varchar(30), -- table name
@strDestinTableName varchar(30) -- table name
AS
BEGIN
DECLARE @strSqlType nvarchar(4000)
DECLARE @strSqlTmp varchar(300)
DECLARE @strSqlSelect varchar(1000)
DECLARE @strSqlFW varchar(1000)
DECLARE @strType varchar(10)
DECLARE @l_cursor cursor
CREATE TABLE #table (TYPE varchar(10) not null)
IF (@strDestinTableName IS null OR @strDestinTableName = '') BEGIN
SET @strDestinTableName = 'TRANSPOSE'
END
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='U' AND name=@strDestinTableName) BEGIN
EXEC('DROP TABLE '+@strDestinTableName)
END
SET @strSqlType = ''
SET @strSqlTmp = 'INSERT INTO #table SELECT DISTINCT '+ @strTranposeField + ' FROM '+ @strSourceTableName
EXEC(@strSqlTmp)
SET @l_cursor = cursor for SELECT TYPE FROM #table ORDER BY TYPE
OPEN @l_cursor
FETCH NEXT FROM @l_cursor INTO @strType
WHILE (@@fetch_status = 0)
BEGIN
--SET @strSqlType = @strSqlType + ',SUM(CASE '+@strTranposeField+' WHEN '''+@strType+''' THEN '+@strCalcuField+' ELSE 0 END) AS '+@strTranposeField+'_'+@strType
SET @strSqlType = @strSqlType + ',SUM(CASE '+@strTranposeField+' WHEN '''+@strType+''' THEN '+@strCalcuField+' ELSE 0 END) AS '+@strType
PRINT @strSqlType
FETCH NEXT FROM @l_cursor INTO @strType
END
CLOSE @l_cursor
DEALLOCATE @l_cursor
--print @strSqlType
--set @strSqlSelect = 'SELECT '+@strGroupByFields+@strSqlType+','+@strOtherCalcuString+' INTO '+@strDestinTableName
SET @strSqlSelect = 'SELECT '+@strSelectFields+@strSqlType
IF (@strOtherCalcuString <> '')
SET @strSqlSelect = @strSqlSelect + ',' + @strOtherCalcuString
SET @strSqlSelect = @strSqlSelect + ' INTO ' + @strDestinTableName
IF (@strWhereCriteria IS NOT NULL AND @strWhereCriteria <>'' )
SET @strWhereCriteria=' AND '+@strWhereCriteria
ELSE
SET @strWhereCriteria = ''
SET @strSqlFW = ' FROM '+ @strSourceTableName +' WHERE 1 =1 ' +@strWhereCriteria+ ' GROUP BY ' + @strGroupByFields + ' ORDER BY ' + @strGroupByFields
-- print @strSqlSelect+@strSqlFW
print (@strSqlSelect+@strSqlFW)
EXEC(@strSqlSelect+@strSqlFW)
EXEC('SELECT * FROM '+@strDestinTableName)
END
GO
****************** END of stored procedure ********************
Here's how to execute the stored procedure with the sample data you've given:
create table SourceTable
(
[User] varchar(10) null,
Element varchar(10) null,
Value numeric(12,2) null,
)
insert into SourceTable values('User1','el1',2.5)
insert into SourceTable values('User1','el2',3.2)
insert into SourceTable values('User2','el1',4.1)
insert into SourceTable values('User2','el3',1.3)
EXEC sp_transpose '[User]','[User]','Element','Value','','','SourceTable',''
Note that I enclosed the User field in brackets because it's a reserved word in SQL Server.
I hope this helps. Cheers!
- Allan
-
Thanks Allan.
That's alot to get thru
I've almost nailed this myself but if I have any more problems I'll pick up some of your stuff.
Much appreciated!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Noel,
I've got this nailed now, thanks very much.
It was made more difficult by the fact that there were 4 "value" fields and depending on which element was getting picked I had to decide which of these 4 "value" fields was the one that contained the value I'm interested in and pick it out. To make it even more difficult the 4 "value" fields were all of different types, including one ofthem being a varchar.
Its been fun though. I like little challenges like this!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply