Solved

CrossTab Query

Posted on 2011-09-13
12
375 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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

695 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