Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# CrossTab Query

Posted on 2011-09-13
Medium Priority
385 Views
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.

experts.png
0
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

LVL 5

Expert Comment

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

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

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

LVL 17

Accepted Solution

Shanmuga Sundaram earned 2000 total points
ID: 36529476

I created a table similar to the above and inserted records in it.

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

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

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

Author Closing Comment

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

Author Comment

ID: 36529655
if you provide this as a single query it will be great!! :))
0

LVL 17

Expert Comment

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

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

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

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

LVL 17

Expert Comment

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

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 */
``````
0

## Featured Post

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A â€¦
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
Is your data getting by on basic protection measures? In todayâ€™s climate of debilitating malware and ransomwareâ€”like WannaCryâ€”that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.â€¦
###### Suggested Courses
Course of the Month12 days, 1 hour left to enroll

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

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