Cross tab - 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 2005 Forums

Transact-SQL (2005)

Cross tab

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-06-17 : 15:56:45

Is it possible change to cross tab using code?

Before code:

NAME TYPE VALUE
AAA HEIGHT 66
AAA WEIGHT 198

After code

NAME HEIGHT WEIGHT
AAA 66 198

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-17 : 16:04:12

Have you even searched for the articles and forums here for crosstab solutions?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 16:05:04

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 01:24:38

Are the cross tab columns static? i.e are you sure you'll always need only values of HEIGHT and WEIGHT Type rows alone?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-18 : 02:58:01

Hi,

try with this

create Table #Temp ( Val varchar(100), names varchar(100),TextValue Int)
Insert Into #Temp
Select 'AAA', 'HEIGHT', 66 Union All
Select 'AAA', 'WEIGHT', 198
Select * From #Temp

Declare @Sql Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @sql = @sql + ', Min(Case when Names = ''' +Names + ''' Then TextValue End ) AS "' + Names + '"'
From (Select distinct Names From #Temp)a
--Select @sql

Select @str = @str + 'Select val as Name '+@sql+' From #Temp Group By val'
print @str
Exec (@str)

Drop Table #Temp

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-06-18 : 13:49:33

I use case like below to do it:

Height = case when type = 'height' then value else null end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources