Solved

Rows to columns

Posted on 2002-06-24
4
446 Views
Last Modified: 2011-04-03
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.
0
Comment
Question by:ronenr
[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
  • 2
4 Comments
 
LVL 7

Expert Comment

by:lozzamoore
ID: 7103316
You can get reasonably close with this:

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

Cheers,
0
 
LVL 2

Author Comment

by:ronenr
ID: 7103321
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)
0
 
LVL 1

Accepted Solution

by:
dmeili earned 50 total points
ID: 7104342
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.
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 2

Author Comment

by:ronenr
ID: 7106582
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.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

628 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