[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2275
  • Last Modified:

Displaying vertical rows into horizontal columns

Hi folks, I am trying to write a query that will Display data from vertical rows into horizontal columns.

Example:

Data in the table
id      col2
----    ----
1       A
1       B
1       D
2       A
2       B
2       C
2       D

The output should be
id    col2  col3  col4  col5
----  ----  ----  ----  ----
1     A     B     null  D
2     A     B     C     D

Assume that the allowable values
for each id are A, B, C and D.

Thanks,
0
zhanghl
Asked:
zhanghl
  • 3
  • 2
1 Solution
 
Gustavo Perez BuenrostroCommented:
Just looking.
0
 
ahoorCommented:
Ok,you wanted a query... I hope you're ready for this...
There is another possibility, and that is using dynamic sql, well, at leats, use sql to create sql statements. The amount of script will however not be less.

I spend some time testing this solution and I can honestly say that at my server it works. However, note that this only works if you have indeed
only 4 possible values for col2.

Ok, here we go:
(ps you can put this all in one stored procedure instead of 2 queries).


create table table_ex
( id       int
, col2   char(1)
)
go

insert into table_ex values(1, 'A')
insert into table_ex values(1, 'B')
insert into table_ex values(1, 'D')
insert into table_ex values(2, 'A')
insert into table_ex values(2, 'B')
insert into table_ex values(2, 'C')
insert into table_ex values(2, 'D')
insert into table_ex values(3, 'A')
insert into table_ex values(3, 'C')
insert into table_ex values(3, 'D')
insert into table_ex values(4, 'A')
go

declare cs_pivot cursor
for    select id, col2
       from   table_ex
       order by col2, id
for    read only
go

begin
declare @v_id     int
,       @v_col   char(1)

open cs_pivot

create table #ordertab
( id    int
, col2  char(1) null
, col3  char(1) null
, col4  char(1) null
, col5  char(1) null
)

fetch cs_pivot into @v_id, @v_col

insert into #ordertab(id, col2)
values (@v_id, @v_col)

fetch cs_pivot into @v_id, @v_col

while @@sqlstatus = 0
begin
   if exists(select 1
             from   #ordertab
             where  col2 = @v_col)
   begin                          -- column already exists
      if exists(select 1
                from   #ordertab
                where  id = @v_id)
      begin                      
         update #ordertab
         set    col2 = @v_col
         where  id = @v_id
      end
      else     -- new id
      begin
         insert into #ordertab (id, col2)
         values (@v_id, @v_col)
      end
   end
   else  -- value not in col 2
   begin
      if exists(select 1
                from   #ordertab
                where  col3 = @v_col)
      begin
         if exists(select 1
                   from   #ordertab
                   where  id = @v_id)
         begin                    
            update #ordertab
            set    col3 = @v_col
            where  id = @v_id
         end
         else
         begin
            insert into #ordertab (id, col3)
            values (@v_id, @v_col)
         end
      end
      else  -- value not in col 2 or 3
      begin
         if exists(select 1
                   from   #ordertab
                   where  col4 = @v_col)
         begin          
            if exists(select 1
                      from   #ordertab
                      where  id = @v_id)
            begin                    
               update #ordertab
               set    col4 = @v_col
               where  id = @v_id
            end
            else
            begin
               insert into #ordertab (id, col4)
               values (@v_id, @v_col)
            end
         end
         else  -- value not in col 2, 3 or 4
         begin
            if exists(select 1
                      from   #ordertab
                      where  col5 = @v_col)
            begin      
               if exists(select 1
                         from   #ordertab
                         where  id = @v_id)
               begin                    
                  update #ordertab
                  set    col5 = @v_col
                  where  id = @v_id
               end
               else        
               begin
                  insert into #ordertab (id, col5)
                  values (@v_id, @v_col)
               end
            end
            else    -- column value does not exist yet, find out
            begin   -- which column is free
               if not exists (select 1
                         from   #ordertab
                         where    col2 != null)
               begin
                  if exists (select 1
                          from   #ordertab
                          where  id = @v_id)
                  begin
                     update #ordertab
                     set    col2 = @v_col
                     where  id = @v_id
                  end
                  else  -- new id
                  begin
                     insert into #ordertab (id, col2)
                     values (@v_id, @v_col)
                  end
               end
               else   -- col2 is in use by a value
               begin  
               if not exists (select 1
                          from   #ordertab
                          where    col3 != null)
               begin
                  if exists (select 1
                             from   #ordertab
                             where  id = @v_id)
                  begin
                     update #ordertab
                     set    col3 = @v_col
                     where  id = @v_id
                  end
                  else  -- new id
                  begin
                     insert into #ordertab (id, col3)
                     values (@v_id, @v_col)
                  end
               end
               else   -- col3 is in use by a value
               begin  
                  if  not exists (select 1
                             from   #ordertab
                             where    col4 != null)
                  begin
                     if exists (select 1
                                from   #ordertab
                                where  id = @v_id)
                     begin
                        update #ordertab
                        set    col4 = @v_col
                        where  id = @v_id
                     end
                     else  -- new id
                     begin
                        insert into #ordertab (id, col4)
                        values (@v_id, @v_col)
                     end
                  end
                  else   -- col4 is in use by a value
                  begin  
                     if not exists (select 1
                                from   #ordertab
                                where    col5 != null)
                     begin
                        if exists (select 1
                                   from   #ordertab
                                   where  id = @v_id)
                        begin
                           update #ordertab
                           set    col5 = @v_col
                           where  id = @v_id
                        end
                        else  -- new id
                        begin
                           insert into #ordertab (id, col5)
                           values (@v_id, @v_col)
                        end
                     end
                     else   -- col5 is in use by a value
                     begin
                        print 'too many values in col2'
                     end
                  end
               end
            end
            end
         end
      end
   end
   fetch cs_pivot into @v_id, @v_col
end
select * from #ordertab
order by id
drop table #ordertab
close cs_pivot
deallocate cursor cs_pivot
end
go
               

Good luck,

Arjan
0
 
zhanghlAuthor Commented:
Ahoor, very thanks for your answer and long coding!

As you mentioned, this solution only works the vertical table has indeed
only 4 possible values for col2, so it is a limited one with T-SQL.

I am looking for a more flexible solition, is it possible that we can solve it with cube or OLAP, although maybe it is based on one specific platform?

Thanks again,
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
ahoorCommented:
Zhangl,

In your original question you stated that it was assumed that col2 only had these 4 possible values.
Else it would be possible only using dynamic sql, except if you solve it at your front-end. For instance Ms-Access has a possibility to pivot values. But I'm not sure how that works on a Sybase database. I used Access once on a MS sql database.

So, on server-level this is the only way, on application level more may be possible.


0
 
zhanghlAuthor Commented:
Ahoor, thanks for your effort. As I mentioned, is it possible that we can solve it with cube or OLAP, although maybe it is based on one specific platform?
0
 
ahoorCommented:
Zhangl
thanks for the points. I don't know what OLAP is but a cube, well, that's not
possible in Sybase I'm afraid. That's why I wrote this query...
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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