Solved

CrossTab Query

Posted on 2011-09-13
12
345 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

20 Experts available now in Live!

Get 1:1 Help Now