Convert Row Values into Column

Question

Post reply

Convert Row Values into Column

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

Mahesh Bote

SSCrazy Eights

Points: 9107

April 4, 2008 at 3:08 am

#798269

[font="Verdana"]Ya Karl, ketikeyan might be missing something. Kartikeyan, let us know.

anyways Karl, thanks 🙂

Mahesh[/font]

MH-09-AM-8694

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Code:

-------------------------------------------------------------------------------

Declare @strSQL VarChar(255)

declare @count int

select @count = 0

Select @count = @count + 1, @strSQL = (Case When @strSQL Is Null Then Date

Else @strSQL +''+Date End )

From Dating

select @strSQL,@count

--------------------------------------------------------------------------------

Please let me know if i did any mistake.

Output :

--------------------------------------------------------------------------------

- -----------

05/JAN/2008 1

--------------------------------------------------------------------------------

karthik

SQLZ SSChampion Points: 12872 More actions · Answer 3

karthikeyan (4/4/2008)

Code:

-------------------------------------------------------------------------------

Declare @strSQL VarChar(255)

declare @count int

select @count = 0

Select @count = @count + 1, @strSQL = (Case When @strSQL Is Null Then Date

Else @strSQL +''+Date End )

From Dating

select @strSQL,@count

--------------------------------------------------------------------------------

Please let me know if i did any mistake.

Output :

--------------------------------------------------------------------------------

- -----------

05/JAN/2008 1

--------------------------------------------------------------------------------

What happens if you do a select * from Dating?

Karl
source control for SQL Server

karthik M One Orange Chip Points: 29139 More actions · Answer 4

karthik M

One Orange Chip

Points: 29139

April 7, 2008 at 2:50 am

#798992

The following rows are displaying.

select * from Dating

Output:

-------------------------------------------

Date

----

01/JAN/2008

02/JAN/2008

03/JAN/2008

04/JAN/2008

05/JAN/2008

-------------------------------------------

karthik

SQLZ SSChampion Points: 12872 More actions · Answer 5

karthikeyan (4/7/2008)

The following rows are displaying.

select * from Dating

Output:

-------------------------------------------

Date

----

01/JAN/2008

02/JAN/2008

03/JAN/2008

04/JAN/2008

05/JAN/2008

-------------------------------------------

Ok,

so what happens if you run this: I get a value of 5 being returned for @count



declare @count int

set @count = 0

select @count = @count + 1 from Dating

select @count

Karl
source control for SQL Server

karthik M One Orange Chip Points: 29139 More actions · Answer 6

karthik M

One Orange Chip

Points: 29139

April 7, 2008 at 3:56 am

#799004

I am getting 1.

karthik

SQLZ SSChampion Points: 12872 More actions · Answer 7

karthikeyan (4/7/2008)

I am getting 1.

That's strange. Can't explain it. What version of SQL are you on? I've tested this on SQL Server 2000 and 2005, with various different compatibility levels (down to 70) and I get the same answer, 5.

Is there another database you can try this on - just as a test?

Karl
source control for SQL Server

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 8

Mahesh Bote

SSCrazy Eights

Points: 9107

April 7, 2008 at 4:10 am

#799012

[font="Verdana"]Please post your DDL of the table, if possible.

Thanks,

Mahesh[/font]

MH-09-AM-8694

karthik M One Orange Chip Points: 29139 More actions · Answer 9

karthik M

One Orange Chip

Points: 29139

April 7, 2008 at 6:05 am

#799046

My Team is using SQL2000 and Sybase12.5.3.

It is not working in Sybase12.5.3.

But the thing is, Sybase and Sqlserver are almost same in some generic query execution.

if we take our situation we are not using any sqlserver based functions.

Please let me know what could be the problem ?

karthik

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 10

[font="Verdana"]

karthikeyan (4/7/2008)

My Team is using SQL2000 and Sybase12.5.3.

It is not working in Sybase12.5.3.

But the thing is, Sybase and Sqlserver are almost same in some generic query execution.

if we take our situation we are not using any sqlserver based functions.

Please let me know what could be the problem ?

Ohhh... my God. Why didn’t you highlight it earlier, that you were trying this example in Sybase? Then what about the example execution in SQL Server? Does it returning the proper resultset?

Mahesh

[/font]

MH-09-AM-8694

karthik M One Orange Chip Points: 29139 More actions · Answer 11

karthik M

One Orange Chip

Points: 29139

April 7, 2008 at 6:32 am

#799058

Sorry for not mentioned it in my earlier post.

Yes it is working fine in Sqlserver2000.

karthik

SQLZ SSChampion Points: 12872 More actions · Answer 12

karthikeyan (4/7/2008)

Sorry for not mentioned it in my earlier post.

Yes it is working fine in Sqlserver2000.

I can't help but see the funny side of this:D

There we all were scratching our heads as to why this wasn't working and it turns out it was working. Just not on sybase:hehe:

Karl
source control for SQL Server

karthik M One Orange Chip Points: 29139 More actions · Answer 13

karthik M

One Orange Chip

Points: 29139

April 7, 2008 at 7:17 am

#799100

Hi SQLZ,

Really sorry !

karthik

Absinthe-428914 Mr or Mrs. 500 Points: 514 More actions · Answer 14

Ok, how about this (and what do I win?):

declare @Dating table

(

Date varchar(25)

)

insert into @Dating

select '01/JAN/2008'

union all

select '02/JAN/2008'

union all

select '03/JAN/2008'

union all

select '04/JAN/2008'

union all

select '05/JAN/2008'

declare @cmd varchar(8000)

declare @comma varchar(1)

declare @num int

set @comma = ''

set @cmd = 'SELECT '

set @num = 1

select @cmd = @cmd + @comma + quotename(date,'''') + ' as ' + quotename(Date)

,@comma = ','

from @dating

exec (@cmd)

set @comma = ''

set @cmd = 'SELECT '

set @num = 1

select @cmd = @cmd + @comma + quotename(date,'''') + ' as C' + convert(varchar,@num)

,@comma = ','

,@num = @num + 1

from @dating

exec (@cmd)

Absinthe-428914 Mr or Mrs. 500 Points: 514 More actions · Answer 15

Absinthe-428914

Mr or Mrs. 500

Points: 514

April 8, 2008 at 9:58 am

#799873

Sorry, I didn't see the other two pages of response before I posted.