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
Solved

crosstab in sql?

Posted on 2002-07-11
6
5,472 Views
Last Modified: 2008-05-23
I have this crosstab query in Access 2000, is it possible to duplicate a crosstab in SQL with a stored procedure?

-----------

TRANSFORM Sum(view3.TotalCount) AS SumOfTotalCount
SELECT view3.MakeID, view3.ModelID, Sum(view3.TotalCount) AS Total
FROM view3
GROUP BY view3.MakeID, view3.ModelID
ORDER BY view3.MakeID, view3.ModelID
PIVOT view3.Type;

thanks
0
Comment
Question by:ptran2000
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7146493
There was an excellent article on this in the September or October 2001 issue of (what used to be) VBPJ (now VSM)

Here is a summary:
Upsize Jet Crosstab Queries
Using Jet crosstabs is a good alternative to PivotTables and data cubes—but SQL Server lacks the operators necessary to generate them. Discover how to migrate your crosstab queries to SQL Server so you can deliver summary data to your users quickly.

Unfortunately, if you do not have a hard copy you may need Premier Membership to read it from archives.  I used to have a (free) link to it, but I believe that has been disabled.

Anthony
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7146579
Here's the TSQL that will duplicate your result (assuming type is an integer field):

select view3.MakeID, view3.ModelID
Sum(Case When Type = 1 Then view3.TotalCount Else 0 End) Type1,
Sum(Case When Type = 2 Then view3.TotalCount Else 0 End) Type2,
Sum(Case When Type = 3 Then view3.TotalCount Else 0 End)Type3,
Sum(Case When Type = 4 Then view3.TotalCount Else 0 End) Type4
-- More lines go here for however many types are possible
From View3
Group By


The only thing is the columns aren't dynamically built for all possible types. You need to find out all possible values for the type field
(Select Distinct Type From View3)
And then write a

Sum(Case When Type = # Then View3.TotalCount Else 0 End) Type#

line for each possible value. This generates the columns.  Now if a new type ever comes about, you'll have to add a new line in the select.  All this can be geneated dynamically; I have seen some code to do that, but it's usually overkill unless you anticipate new types to be generated frequently . . . .
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7146595
Oops, I left out the group by fields. Here's the statement:

select view3.MakeID, view3.ModelID
Sum(Case When Type = 1 Then view3.TotalCount Else 0 End) Type1,
Sum(Case When Type = 2 Then view3.TotalCount Else 0 End) Type2,
Sum(Case When Type = 3 Then view3.TotalCount Else 0 End)Type3,
Sum(Case When Type = 4 Then view3.TotalCount Else 0 End) Type4
-- More lines go here for however many types are possible
From View3
Group By view3.MakeID, view3.ModelID
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Accepted Solution

by:
curtis591 earned 50 total points
ID: 7146612
Set the parameter to this stored procedure and it will do it for you automatically.

CREATE procedure sp_CrossTab
     @tablename               varchar(255),     -- Table/View on which to perform the cross tab query.
     @crosscolumn          varchar(255),     -- Attribute to be used as columns in the cross tab.
     @crossrow               varchar(255),     -- Attribute to be used as rows in the cross tab.
     @crossvalue               varchar(255)     -- Attribute to be used as value in the cross tab.
As

-- Work variables
declare
     @sql                    varchar(8000),     -- Hold the dynamically created sql statement
     @colname               varchar(255),     -- The current column when building sql statement
     @i                         smallint,          -- know when we reached the last column (@i = @cols)
     @cols                    smallint,          -- Number of columns
     @longest_col          smallint,          -- the len() of the widest column
     @CrLf                    char(2)
-- Constants
declare
     @max_cols_in_table     smallint,
     @max_col_name_len     smallint,
     @max_statement_len     smallint,
--     @sql7                    bit,               -- 1 when version 7, 0 otherwise.
     @err_severity          int

set nocount on

set @max_cols_in_table     = 255
set @max_statement_len     = 8000
set @max_col_name_len     = 128
set @err_severity          = 11
set @CrLf = char(13) + char(10)


-- Check inputs
if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
     raiserror ('Missing parameter(s)!',@err_severity,1)
     return 0
end

-- Check for existence of the table.
if (not exists(select * from sysobjects where name like @tablename))begin
     raiserror ('Table/View for crosstab not found!',@err_severity,1)
     return 0
end

-- Don't check for columns because we may actually get an expression as the column name

-- prepare for future feature of checking database version to validate
-- inputs. Default to version 7
--set @sql7 = 1
--if (patindex('%SQL Server  7.%',@@version) = 0) begin
--     set @sql7 = 0
--end

-- Extract all values from the rows of the attribute
-- we want to use to create the cross column. This table
-- will contain one row for each column in the crosstab.
create table #crosscol (crosscolumn varchar(255))
set @sql =     ' insert #crosscol Select Distinct ' + @crosscolumn +
               ' From ' + @tablename --+
               --' Group By ' + @crosscolumn
--print @sql
exec (@sql)
set @cols = @@rowcount

if @cols > @max_cols_in_table begin
     raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1)
     return 0
end
else begin
     if @cols = 0 begin
          raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1)
          return 0
     end
     else begin
          -- Check if any of the data is too long to make it a name of a column
          select @longest_col = max(len(convert(varchar(129),crosscolumn)))
          from #crosscol

          if @longest_col > @max_col_name_len begin
               raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
               return 0
          end
          else begin
               
               -- All Validations OK, start building the dynamic sql statement

               set @sql = ''
               -- Use tmp table rows to create the sql statement for the crosstab.
               -- each row in the table will be a column in the cross-tab
               set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As '
                              + @crossrow + ', ' + @CrLf + space(4)

               --set @sql = 'select ' + @crossrow + ', ' + char(13)
               
               declare cross_sql cursor for
               select crosscolumn
               from #crosscol
               order by crosscolumn

               --print 'Sql cross statment: ' +  @sql

               open cross_sql
               fetch next from cross_sql into @colname
               -- Use "@i" to check for the last column. We need to input commas
               -- between columns, but not after the last column
               set @i = 0
               while @@FETCH_STATUS = 0 begin
                    set @i = @i + 1
                    set @colname = isnull(@colname,'Undefined')
                    set @crossvalue = isnull(@crossvalue, 0)

                    Set @sql = @sql  + '''' +  
                         convert(varchar(128), @colname) +
                         ''' = sum(case convert(varchar(128), ' + @crosscolumn + ')'
                              + char(13) + char(10) + space(8) +
                              ' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '

                    if @i < @cols
                         set @sql = @sql + ', ' + @CrLf + space(4)
                    else
                         set @sql = @sql + @CrLf

                    fetch next from cross_sql into @colname
                    end

               close cross_sql
               deallocate cross_sql

               set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow
               
               if len(@sql) >= @max_statement_len begin
                    raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,1)
                    return 0
               end

               exec (@sql)
               Select 'Sql' =  @sql
               set nocount off              
               return 1

          end
     end
end

GO
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7147042
Yeah that's the dynamic version. Depends on how often you expect new attributes (in this case types) to be added. If the types are static, write you own pivot query and be done with it.  But if you anticipate new types occuring frequently use the dynamic code.  =)
0
 

Author Comment

by:ptran2000
ID: 7537699
I can't remember what happened to this question, but I figure your answer was the longest and probably most time consuming.

thx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

791 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