Solved

CrossTab Query

Posted on 2011-09-13
12
335 Views
Last Modified: 2012-06-22
Hello Experts,

I have a db table (tbCriteriaResults) such as:

- StoreID
- Criteria No
- Result
- Position

 

I want to create a crosstab resultset such as:

- StoreID
- Criteria No 9
- Criteria Position 9
- Criteria No 10
- Criteria Position 10
.......
- Performance Point
 

As you can see , performance point is a calculated column. And it sums all values of criteria columns. For example for Store ID 1:

10+5= 15

For store ID 2:

12+6 =18

 
I have attached a screenshot and you will exactly understand what I want.

Please help... How can I do this?


experts.png
0
Comment
Question by:innocent1973
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36529222
Hi,

what you need is the SQL PIVOT command

http://msdn.microsoft.com/en-us/library/ms177410.aspx


give it a go and if you get stuck come back to us

Thanks

Dave
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36529230
Had this from net a very long ago.

create PROCEDURE [crosstab]      
@select varchar(8000),      
@sumfunc varchar(100),      
@pvot varchar(100),      
@table varchar(100)      
AS      
DECLARE @sql varchar(8000), @delim varchar(1)      
SET NOCOUNT ON      
SET ANSI_WARNINGS OFF      
EXEC ('SELECT ' + @pvot + ' AS pvot INTO ##pvot FROM ' + @table + ' WHERE 1=2')      
EXEC ('INSERT INTO ##pvot SELECT DISTINCT ' + @pvot + ' FROM ' + @table + ' WHERE ' + @pvot + ' Is Not Null order by ' +@pvot )      
SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )      
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )      
WHEN 0 THEN '' ELSE '''' END      
FROM tempdb.information_schema.columns WHERE table_name='##pvot' AND column_name='pvot'      
SELECT @sql=@sql + '''' + convert(varchar(100), pvot) + ''' = ' +        
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pvot + ' WHEN '      
+ @delim + convert(varchar(100), pvot) + @delim + ' THEN ' ) + ', ' FROM ##pvot      
DROP TABLE ##pvot      
SELECT @sql=left(@sql, len(@sql)-1)      
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')      
EXEC (@select)      
SET ANSI_WARNINGS ON

To execute this crosstab the query would look like

crosstab 'SELECT  Country as country FROM company group by Country' , 'count(icode)' , 'status','company'
0
 

Author Comment

by:innocent1973
ID: 36529346
Oh god!
How can I apply this complex query to my case? :)))))
0
 
LVL 17

Accepted Solution

by:
Shanmuga Sundaram earned 500 total points
ID: 36529476
Let me try to help you.

I created a table similar to the above and inserted records in it.
 table
Now I created the stored procedure that I provided above

Now I execute the stored procedure

execute crosstab 'SELECT   storeid FROM tblcriterias group by storeid' , 'sum(result)'   , '''Criteria No ''+convert(varchar(12),criteriano)','tblcriterias'

and this returned

 one part
now I executed the stored procedure

exec crosstab 'SELECT   storeid FROM tblcriterias group by storeid' , 'sum(position)' , '''Criteria Position'' +convert(varchar(12),criteriano)','tblcriterias'

and this returned

 next part
Now I am trying how to make this as a single query.
0
 

Author Closing Comment

by:innocent1973
ID: 36529646
Thank you very much for your patience and great helps
0
 

Author Comment

by:innocent1973
ID: 36529655
if you provide this as a single query it will be great!! :))
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36530301
A single stored procedure to get the results


Create Procedure CustomCrosstab as
DECLARE @sql varchar(8000), @delim varchar(1),@select varchar(8000),@sumfunc varchar(100),@pvot varchar(100),@table varchar(100)      
set @select ='SELECT   storeid FROM tblcriterias group by storeid'
set @sumfunc ='sum(result)'
set @pvot= '''Criteria No ''+convert(varchar(12),criteriano)'
set @table ='tblcriterias'
SET NOCOUNT ON      
SET ANSI_WARNINGS OFF      
EXEC ('SELECT ' + @pvot + ' AS pvot INTO ##pvot FROM ' + @table + ' WHERE 1=2')      
EXEC ('INSERT INTO ##pvot SELECT DISTINCT ' + @pvot + ' FROM ' + @table + ' WHERE ' + @pvot + ' Is Not Null order by ' +@pvot )      
SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )      
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )      
WHEN 0 THEN '' ELSE '''' END      
FROM tempdb.information_schema.columns WHERE table_name='##pvot' AND column_name='pvot'      
SELECT @sql=@sql + '''' + convert(varchar(100), pvot) + ''' = ' +        
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pvot + ' WHEN '      
+ @delim + convert(varchar(100), pvot) + @delim + ' THEN ' ) + ', ' FROM ##pvot      
DROP TABLE ##pvot      
SELECT @sql=left(@sql, len(@sql)-1)      
DROP TABLE ##TEMP
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' into ##temp ')      
EXEC (@select)      
set @select = 'SELECT   storeid FROM tblcriterias group by storeid'
set @sumfunc = 'sum(position)'
set @pvot= '''Criteria Position'' +convert(varchar(12),criteriano)'
set @table ='tblcriterias'
SET NOCOUNT ON      
SET ANSI_WARNINGS OFF      
EXEC ('SELECT ' + @pvot + ' AS pvot INTO ##pvot FROM ' + @table + ' WHERE 1=2')      
EXEC ('INSERT INTO ##pvot SELECT DISTINCT ' + @pvot + ' FROM ' + @table + ' WHERE ' + @pvot + ' Is Not Null order by ' +@pvot )      
SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )      
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )      
WHEN 0 THEN '' ELSE '''' END      
FROM tempdb.information_schema.columns WHERE table_name='##pvot' AND column_name='pvot'      
SELECT @sql=@sql + '''' + convert(varchar(100), pvot) + ''' = ' +        
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pvot + ' WHEN '      
+ @delim + convert(varchar(100), pvot) + @delim + ' THEN ' ) + ', ' FROM ##pvot      
DROP TABLE ##pvot      
SELECT @sql=left(@sql, len(@sql)-1)      
      DROP TABLE ##temp1      
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' into ##temp1 ')      
EXEC (@select)  
select * from ##temp a,##temp1 b where a.storeid=b.storeid
SET ANSI_WARNINGS ON
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36530389
Guys, I'd like to point out this is very inefficient code, I'd imagine written pre sql 2005 when the PIVOT statement was brought in.

I'd still strongly advise you to look at the PIVOT statement in my original post


Thanks

Dave
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36531433
And Finally here is a single procedure for your need.


Alter Procedure CustomCrosstab as
DECLARE @sql varchar(8000), @delim varchar(1),@select varchar(8000),@sumfunc varchar(100),@pvot varchar(100),@table varchar(100)      
set @select ='SELECT   storeid FROM tblcriterias group by storeid'
set @sumfunc ='sum(result)'
set @pvot= '''Criteria No ''+convert(varchar(12),criteriano)'
set @table ='tblcriterias'
SET NOCOUNT ON      
SET ANSI_WARNINGS OFF      
EXEC ('SELECT ' + @pvot + ' AS pvot INTO ##pvot FROM ' + @table + ' WHERE 1=2')      
EXEC ('INSERT INTO ##pvot SELECT DISTINCT ' + @pvot + ' FROM ' + @table + ' WHERE ' + @pvot + ' Is Not Null order by ' +@pvot  )      
SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )      
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )      
WHEN 0 THEN '' ELSE '''' END      
FROM tempdb.information_schema.columns WHERE table_name='##pvot' AND column_name='pvot'      
SELECT @sql=@sql + '''' + convert(varchar(100), pvot) + ''' = ' +        
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pvot + ' WHEN '      
+ @delim + convert(varchar(100), pvot) + @delim + ' THEN ' ) + ', ' FROM ##pvot      
DROP TABLE ##pvot      
SELECT @sql=left(@sql, len(@sql)-1)      
DROP TABLE ##TEMP
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' into ##temp ')      
EXEC (@select)      
alter table ##temp add [Performance Point] varchar(10)
Set @SQL =''
SELECT @SQL =@SQL+ 'ISNULL(['+COLUMN_NAME + '],0) + ' FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '##temp' and COLUMN_NAME <> 'storeid'
SELECT @SQL = LEFT(@SQL, len(@SQL) - 2)
Set @SQL = 'update ##temp set [Performance Point]= '+ @SQL
EXEC (@SQL)
set @select = 'SELECT   storeid FROM tblcriterias group by storeid '
set @sumfunc = 'sum(position)'
set @pvot= '''Criteria Position '' +convert(varchar(12),criteriano)'
set @table ='tblcriterias'
SET NOCOUNT ON      
SET ANSI_WARNINGS OFF      
EXEC ('SELECT ' + @pvot + ' AS pvot INTO ##pvot FROM ' + @table + ' WHERE 1=2')      
EXEC ('INSERT INTO ##pvot SELECT DISTINCT ' + @pvot + ' FROM ' + @table + ' WHERE ' + @pvot + ' Is Not Null order by ' +@pvot )      
SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )      
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )      
WHEN 0 THEN '' ELSE '''' END      
FROM tempdb.information_schema.columns WHERE table_name='##pvot' AND column_name='pvot'      
SELECT @sql=@sql + '''' + convert(varchar(100), pvot) + ''' = ' +        
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pvot + ' WHEN '      
+ @delim + convert(varchar(100), pvot) + @delim + ' THEN ' ) + ', ' FROM ##pvot      
DROP TABLE ##pvot      
SELECT @sql=left(@sql, len(@sql)-1)      
      DROP TABLE ##temp1      
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' into ##temp1 ')      
EXEC (@select)  
Set @SQL ='Select a.storeid as storeid,'
SELECT  @SQL = @SQL+  '['+COLUMN_NAME + '] , '  FROM tempdb.INFORMATION_SCHEMA.COLUMNS where table_name like '##temp%'  and column_name <>'storeid' Order by COLUMN_NAME
SELECT @SQL = LEFT(@SQL, len(@SQL) - 2)
SELECT @SQL =@SQL + ' from ##temp a,##temp1 b where a.storeid=b.storeid'
Exec (@SQL)
SET ANSI_WARNINGS ON
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36531487
I would have to agree with Dave. If you need it to be dynamic, see http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html.

However, from your example, it appears to be very simple with the exception that you have to PIVOT twice: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1537-Broaden-Your-Horizons-Pivot-Again.html

If anything and you only have those two columns, i.e., 9 and 10, then you could just do a simple condition aggregate and be done with it. However, here is an example of the multiple PIVOTs you will need.
/* WITH ... () can be removed; just used for sample data */
;WITH your_table(StoreID, [Criteria No], Result, Position) AS 
(
   SELECT '0001', 9, 10, 1
   UNION SELECT '0002', 9, 12, 2
   UNION SELECT '0001', 10, 5, 1
   UNION SELECT '0002', 10, 6, 2
)
SELECT pvt_a.StoreID
     , pvt_a.[9] AS [Criteria No 9]
	 , pvt_b.[9] AS [Position No 9]
	 , pvt_a.[10] AS [Criteria No 10]
     , pvt_b.[10] AS [Position No 10]
	 , (pvt_a.[9] + pvt_a.[10]) AS [Performance Point]
FROM (SELECT StoreID, [Criteria No], Result FROM your_table) a
PIVOT (SUM([Result]) FOR [Criteria No] IN ([9], [10])) pvt_a
JOIN (SELECT StoreID, [Criteria No], Position FROM your_table) b
PIVOT (MIN([Position]) FOR [Criteria No] IN ([9], [10])) pvt_b
   ON pvt_b.StoreID = pvt_a.StoreID

Open in new window

0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36535664
This uses Single PVOT with dynamic query generation. I tried this with multiple stores and the results are correct

Alter Procedure CustomCrosstab as  
declare @SQL varchar(max),@SQL1 varchar(max),@SQL2 varchar(max),@refid varchar(10),@id int  
set @refid =right(replace(replace(replace(getdate(),'-',''),' ' ,''),':',''),10)  
Create TABLE #log_table (refid varchar(10),SQL text)
Set @SQL ='SELECT pvt_a.StoreID'  
SELECT @SQL =@SQL+   ',pvt_a.[' +convert(varchar(5), [CriteriaNo])+'] AS [Criteria No '+convert(varchar(5), [CriteriaNo])+']'  + char(13)  
+   ',pvt_b.[' +convert(varchar(5), [CriteriaNo])+'] AS [Postion No '+convert(varchar(5), [CriteriaNo])+']'    
FROM  tblcriterias group by criteriano  
Insert into #log_table values (@refid,@SQL)  
set @SQL=' ,('
SELECT @SQL = @SQL +  'ISNULL(pvt_a.[' +convert(varchar(5),[CriteriaNo])+'],0)+' FROM  tblcriterias group by criteriano
SELECT @sql=left(@sql, len(@sql)-1)  
SELECT @sql=@SQL + ' ) AS [Performance Point]'
update #log_table set SQL= convert(varchar(8000),SQL)  +convert(varchar(8000),@SQL)    where refid =@refid  
set @SQL=''  
select @SQL = @SQL + ' FROM (SELECT StoreID, [CriteriaNo], Result FROM tblcriterias) a PIVOT (SUM([Result]) FOR [CriteriaNo] IN (' + char(13)  
SELECT @SQL =@SQL+   '[' +convert(varchar(5),[CriteriaNo])+'],' FROM  tblcriterias group by criteriano  
SELECT @sql=left(@sql, len(@sql)-1)        
SELECT @SQL =@SQL+   ')) pvt_a '  
update #log_table set SQL=   convert(varchar(8000),SQL)  +convert(varchar(8000),@SQL)    where refid =@refid
set @SQL=''  
select @SQL = @SQL + ' JOIN (SELECT StoreID, [CriteriaNo], Position FROM tblcriterias) b '  
select @SQL = @SQL + ' PIVOT (MIN([Position]) FOR [CriteriaNo] IN ( '  
SELECT @SQL =@SQL+   '[' +convert(varchar(5),[CriteriaNo])+'],' FROM  tblcriterias group by criteriano  
SELECT @sql=left(@sql, len(@sql)-1)        
SELECT @SQL =@SQL+   ')) pvt_b '  
update #log_table set SQL= convert(varchar(8000),SQL)  +convert(varchar(8000),@SQL)    where refid =@refid  
set @SQL='ON pvt_b.StoreID = pvt_a.StoreID'  
update #log_table set SQL= convert(varchar(8000),SQL)  +convert(varchar(8000),@SQL)    where refid =@refid  
set @sql =(select substring(sql,1,8000) from #log_table where refid =@refid)  
set @sql1 =(select substring(sql,8001,8000) from #log_table where refid =@refid)  
set @sql2 =(select substring(sql,16001,8000) from #log_table where refid =@refid)  
execute (@SQL + @sql1 + @sql2)  
drop table #log_table
 
 



0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36536785
"This uses Single PVOT with dynamic query generation." < No it does NOT. You simply copied my code and made it into dynamic SQL. There are still two PIVOTs in it aliased as pvt_a and pvt_b as I did; therefore, I am not sure why you felt the need to post that unless your aim was to plagiarize my code w/o my permission and piss me off. If so, you succeeded.

Here is how I would do this with SQL 2005/2008.
DECLARE @criteria_tbl VARCHAR(50);
/* replace this and two FOR XML statements w/ correct tablename */
SET @criteria_tbl = '#tblcriterias';
DECLARE @criteria_nos VARCHAR(MAX), @pivot_cols VARCHAR(MAX), @sql VARCHAR(MAX);

/* generate value list from distinct criteria numbers */
SET @criteria_nos = STUFF((
   SELECT ',['+CONVERT(VARCHAR(10), [Criteria No])+']'
   FROM #tblcriterias
   GROUP BY [Criteria No]
   ORDER BY [Criteria No] 
   FOR XML PATH('')
), 1, 1, '')
;

/* generate column list for distinct numbers */
SET @pivot_cols = STUFF((
   SELECT ', pvt_alias.['+CONVERT(VARCHAR(10), [Criteria No])+'] AS [result_alias No ' + CONVERT(VARCHAR(10), [Criteria No]) +']'
   FROM #tblcriterias
   GROUP BY [Criteria No]
   ORDER BY [Criteria No] 
   FOR XML PATH('')
), 1, 1, '')
;

/* build dynamic SQL script */
SET @sql = 'SELECT pvt_a.StoreID
     , '+REPLACE(REPLACE(@pivot_cols, 'pvt_alias', 'pvt_a'), 'result_alias', 'Criteria')+'
	 , '+REPLACE(REPLACE(@pivot_cols, 'pvt_alias', 'pvt_b'), 'result_alias', 'Position')+'
	 , (pvt_a.'+REPLACE(@criteria_nos, ',', '+pvt_a.')+') AS [Performance Point]
FROM (SELECT StoreID, [Criteria No], Result FROM ['+@criteria_tbl+']) a
PIVOT (SUM([Result]) FOR [Criteria No] IN ('+@criteria_nos+')) pvt_a
JOIN (SELECT StoreID, [Criteria No], Position FROM ['+@criteria_tbl+']) b
PIVOT (MIN([Position]) FOR [Criteria No] IN ('+@criteria_nos+')) pvt_b
   ON pvt_b.StoreID = pvt_a.StoreID
;';

PRINT(@sql); /* audit dynamic SQL script */
EXEC(@sql); /* execute dynamic SQL script */

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now