?
Solved

CrossTab Query

Posted on 2011-09-13
12
Medium Priority
?
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 17

Accepted Solution

by:
Shanmuga Sundaram earned 2000 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
 
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 60

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 60

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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

801 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