• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

Rows to columns

Is there any way to convert a row to a column? for example:

Table A
F1     F2      F3
1      A       Data1
1      B       Data2
1      C       Data3
2      A       Data10
2      B       Data20
2      C       Data30

Result Table:
A      B       C
Data1  Data2   Data3
Data10 Data20  Data30

How you can see the rows in Table A now are fields in the result table.

I am looking for a way to do it in SQL Server 7.0 or 2000 using a store proc or something like that.

Thanks in advance.
  • 2
1 Solution
You can get reasonably close with this:

case f2 when 'A' then f3 end as A,
case f2 when 'B' then f3 end as B,
case f2 when 'C' then f3 end as C
from tablea

but this gives you lots of nulls!

Let me delve a little deeper.

ronenrAuthor Commented:
Is there something dynamic, because I donot know the data in the table A, today it can be (A,B,C) and tomorrow it can be something else (YY,HH,PP)
I found code somewhere on this site to do it dynamically but I can't remember who the author was. If the author comes forward give the points to him/her.

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.

-- Work variables
     @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
     @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

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

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

-- 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
else begin
     if @cols = 0 begin
          raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1)
          return 0
     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
          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)
                         set @sql = @sql + @CrLf

                    fetch next from cross_sql into @colname

               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

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


ronenrAuthor Commented:
Thanks a lot I tried the SP and with some adaptations works fine, my values can be numbers or chars so I have to change the sum to min, etc.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now