Row to Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Row to Column

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-06-03 : 01:02:27

Dear Al,

Before
------------------------
FieldCode | FieldDesc
------------------------
Name | Nama
Age | Umur
:
:

After
----------------
Name | Age | .....
---------------
Nama | Umur

Anyone know how to write sql query without using select case, i want it to be auto, coz the fieldcode can be many. I'm using Ms SQL 2000. If in 2005 i know can used pivot method.

Please Advise.
Thank you.

Regards,
Michelle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-03 : 01:14:15

try this http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-03 : 02:07:48

if the number of rows need to transpose is unknown then you have to use Dynamic SQL to do the job.

Refer here http://www.sommarskog.se/dynamic_sql.html for more information on Dynamic SQL.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-03 : 04:00:33

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-06-04 : 22:16:10

Dear Madhivanan,

Thank you so much for your sharing. I'm in 80% of stage in finishing my solution. Could you pls help me, how am i going to alter the stored procedures to get after result as below?

Before:
----------

EXEC CrossTab
'SELECT labeltext,fieldcode from TBLFIELDDESC where linenoid=115000 ',
'fieldcode',
'max(labeltext)[]',
'fieldcode'

Fieldcode Full Registration SponsorCode
----------------------------------------------------------------
Full Registration Full Registration NULL
SponsorCode NULL Sponsor Code

After:
-------

p/s:Don't want to show fieldcode, and max for the rest column.
How could i get the result in this way:-

Full Registration SponsorCode
Full Registration Sponsor Code

Please Advise.

Thank you.

Regards,
Michelle

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-06-04 : 23:01:54

Dear Sir Madhivanan,


select
max(CASE WHEN fieldcode='Full Registration' THEN labeltext END) as [Full Registration], 
max(CASE WHEN fieldcode='SponsorCode' THEN labeltext END) as [SponsorCode]
from
(
SELECT labeltext,fieldcode from TBLFIELDDESC where linenoid=115000
) T
group by fieldcode

Output I'm getting as below:
Full Registration SponsorCode
-----------------------------------
Full Registration Null
Null SponsorCode

How can i just get the output as:-

Full Registration SponsorCode
----------------------------------
Full Registration SponsorCode

Modification of stored procedures as below:-



Alter procedure CrossTab2  
/*--  
EXEC CrossTab2 
'SELECT labeltext,fieldcode from TBLFIELDDESC where linenoid=115000 ',  
'fieldcode',  
'max(labeltext)[]',  
'fieldcode'   
--*/   
(    
 @select varchar(2000),    
 @PivotCol varchar(100),     
 @Summaries varchar(100),      
 @GroupBy varchar(100)    
 --@OtherCols varchar(100) = Null    
)    
    
AS    
    
set nocount on    
set ansi_warnings off    
    
declare @sql varchar(8000)    
declare @sql1 varchar(8000) 
    
Select @sql = ''    
--Select @OtherCols= isNull(', ' + @OtherCols,'')    
    
    
create table #pivot_columns  (pivot_column_name varchar(100))    
Select @sql='select ''' + replace( + @PivotCol,',','''   as pivot_column_name union all select ''')+''''    
insert into #pivot_columns    
exec(@sql)    
    
select @sql=''    
    
create table #pivot_columns_data  (pivot_column_name varchar(100),pivot_column_data varchar(100))    
    
Select @PivotCol=''    
Select @PivotCol=max(pivot_column_name) from #pivot_columns    
While @PivotCol>''    
Begin    
 insert into #pivot_columns_data(pivot_column_name,pivot_column_data)     
 exec  (    
  'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data from     
   ('+    
    @select    
   +'    
   ) T'    
  )    
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol    
    
end    
    
    
--select @sql = @sql + ', ' + 
select @sql = @sql + ', ' +       
  replace(    
  replace(    
   @Summaries,
   '(','(CASE WHEN ' + Pivot_Column_name + '=''' +     
    pivot_column_data +  ''' THEN '     
    ),    
   ')[', ' END) as [' + pivot_column_data     
   )    
from #pivot_columns_data    
order by pivot_column_name 
   
select @sql1= right(@sql,len(@sql)-1)

  

--print @sql

/*--
select
max(CASE WHEN fieldcode='Full Registration' THEN labeltext END) as [Full Registration], 
max(CASE WHEN fieldcode='SponsorCode' THEN labeltext END) as [SponsorCode]
from
(
SELECT labeltext,fieldcode from TBLFIELDDESC where linenoid=115000
) T
group by fieldcode
--*/

--exec ( 'select ' + @GroupBy +@sql +      
exec ( 'select ' + @sql1 +     
    
       ' from (    
  '+    
   @select     
  +'    
  ) T    
 GROUP BY ' + @GroupBy)    
    
    
drop table #pivot_columns    
drop table #pivot_columns_data    
    
set nocount off    
set ansi_warnings on

Regards,
Michelle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 23:02:13

you always need to show 2 columns in your result or the number of columns may varies ?

KH
[spoiler]Time is always against us[/spoiler]

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-06-04 : 23:33:11

Dear All,

Thank you. I found the solution:-

Here is the successful stored procedures


Alter procedure CrossTab2  
/*--  
EXEC CrossTab2 
'SELECT labeltext,fieldcode from TBLFIELDDESC where linenoid=115000 ',  
'fieldcode',  
'max(labeltext)[]',  
'fieldcode'   
--*/   
(    
 @select varchar(2000),    
 @PivotCol varchar(100),     
 @Summaries varchar(100),      
 @GroupBy varchar(100)    
 --@OtherCols varchar(100) = Null    
)    
    
AS    
    
set nocount on    
set ansi_warnings off    
    
declare @sql varchar(8000)    
declare @sql1 varchar(8000) 
    
Select @sql = ''    
--Select @OtherCols= isNull(', ' + @OtherCols,'')    
    
    
create table #pivot_columns  (pivot_column_name varchar(100))    
Select @sql='select ''' + replace( + @PivotCol,',','''   as pivot_column_name union all select ''')+''''    
insert into #pivot_columns    
exec(@sql)    
    
select @sql=''    
    
create table #pivot_columns_data  (pivot_column_name varchar(100),pivot_column_data varchar(100))    
    
Select @PivotCol=''    
Select @PivotCol=max(pivot_column_name) from #pivot_columns    
While @PivotCol>''    
Begin    
 insert into #pivot_columns_data(pivot_column_name,pivot_column_data)     
 exec  (    
  'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data from     
   ('+    
    @select    
   +'    
   ) T'    
  )    
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol    
    
end    
    
    
--select @sql = @sql + ', ' + 
select @sql = @sql + ', ' +       
  replace(    
  replace(    
   @Summaries,
   '(','(CASE WHEN ' + Pivot_Column_name + '=''' +     
    pivot_column_data +  ''' THEN '     
    ),    
   ')[', ' END) as [' + pivot_column_data     
   )    
from #pivot_columns_data    
order by pivot_column_name 
   
select @sql1= right(@sql,len(@sql)-1)

  

--print @sql

/*--

select [FullRegistration],[SponsorCode]
(
select
max(CASE WHEN fieldcode='Full Registration' THEN labeltext END) as [FullRegistration], 
max(CASE WHEN fieldcode='SponsorCode' THEN labeltext END) as [SponsorCode]
from
(
SELECT labeltext,fieldcode from TBLFIELDDESC where linenoid=115000
) T
group by fieldcode
)e
group by [Full Registration],[SponsorCode]


--*/

--exec ( 'select ' + @GroupBy +@sql +      
exec ( 'select ' + @sql1 +    
    
       '  from (    
  '+    
   @select     
  +'    
  ) T '   
 )    



 
    
drop table #pivot_columns    
drop table #pivot_columns_data    
    
set nocount off    
set ansi_warnings on

Regards,
Michelle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources