Convert Row Values into Column

Question

Post reply

Convert Row Values into Column

karthik M

One Orange Chip

Points: 29139
More actions
April 2, 2008 at 4:10 am

#76939

Dear Experts,

I had sent one scenario.

Ex: select * from dating

Value date

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

2007/03/20

2007/04/20

2007/04/20

I want a table like this:

select * from columntable:

Output:

2007/03/20 2007/04/20 2007/05/20

But I don’t know how many rows are there in dating table. Inputs are higly appreciable.

karthik

Viewing 15 posts - 1 through 15 (of 33 total)

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

SQLZ

SSChampion

Points: 12872

April 2, 2008 at 4:39 am

#797090

Check out this article - it should get you started on the right track: http://qa.sqlservercentral.com/articles/Test+Data/61572/[/url]

Karl
source control for SQL Server

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

I found one solution,But i don't know whether it is correct or not.

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

create table Dating

(

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'

--drop table #Dating

Select ID = IDENTITY(1,1,Int),Date into #Dating

from Dating

Declare @Start int,@Cnt int,@Str varchar(255)

select @Cnt = Count(Date),@Start = 1

from #Dating

while @Start <= @Cnt

Begin

select @STR = @STR +''+Date

from #Dating

where ID = @Start

Select @Start = @Start + 1

End

select @STR

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

karthik

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

Mahesh Bote

SSCrazy Eights

Points: 9107

April 2, 2008 at 6:20 am

#797136

[font="Verdana"]

This way also ...

Declare @strSQL VarChar(Max)

Select @strSQL = (Case When @strSQL Is Null Then {Date Col} Else @strSQL + ', ' + {Date Col} End)

From {Table}

Select @strSQL

Thanks,

Mahesh

[/font]

MH-09-AM-8694

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

karthik M

One Orange Chip

Points: 29139

April 2, 2008 at 6:26 am

#797141

I think we can't use CASE statement. Because we don't know the exact row count.

Note:

The output should be stored in each column.

karthik

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

karthik M

One Orange Chip

Points: 29139

April 2, 2008 at 6:35 am

#797152

Hi,

Thanks for your interest.

But it is giving the last row only.

Code:

Declare @strSQL VarChar(255)

Select @strSQL = Case When @strSQL is null then Date else @strSQL + ' ' + Date End

From Dating

Select @strSQL

Output:

05/Jan/2008

karthik

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

[font="Verdana"]

Kartikeyan, sorry to say but you must be missing something. I have tried the sample query and then posted it. My sample query is:

declare @STR varchar(7999)

Select @STR = (Case When @STR Is Null Then Product_Desc Else @STR + ', ' + Product_Desc End

From Invoice_Details

Where product_key is not null

Select @STR

and its giving me, correct result, all the Product descriptions with COMMA [,] separated list in a one row one coloumn.

Please confirm.

Thanks,

Mahesh

[/font]

MH-09-AM-8694

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

Mahesh,

It is not working.I don't know why ?

Table Structure:

create table Dating

(

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'

Query:

Declare @strSQL VarChar(255)

Select @strSQL = ''

select @strSQL =case when @strSQL = '' then Date else @strSQL + ', ' + Date End

From Dating

select @strSQL

karthik

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

I got the following output:

, 05/JAN/2008

My Requirement is

I want to display the row in seperate column.

Ex:

Table Name : Dating

Date

01/JAN/2008

02/JAN/2008

03/JAN/2008

Expected output:

C1 C2 C3

01/JAN/2008 02/JAN/2008 03/JAN/2008

Suppose if we have 5 rows in the Dating table, expected output would be

C1 C2 C3 C4 C5

01/JAN/2008 02/JAN/2008 03/JAN/2008 04/JAN/2008 05/JAN/2008

Thing is number of rows in that table is unknown.

Inputs are welcome !

karthik

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

karthik M

One Orange Chip

Points: 29139

April 4, 2008 at 1:13 am

#798229

Any inputs ?

karthik

SQLZ SSChampion Points: 12872 More actions · Answer 10

karthikeyan (4/4/2008)

Any inputs ?

If you modify Mahesh's solution just a little bit you've got your answer:



Declare @strSQL VarChar(Max)

declare @count int

set @count = 0

Select @count = @count + 1, @strSQL = (Case When @strSQL Is Null Then '''' + Date + ''' as C1' Else @strSQL + ', ' + '''' + Date + ''' as C' + convert(varchar, @count) End)

From Dating

Select @strSQL = 'select ' + @strSQL

exec (@strSQL)

Karl
source control for SQL Server

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

karthik M

One Orange Chip

Points: 29139

April 4, 2008 at 2:19 am

#798250

SQLZ,

It is not working.

i got the following output when i execute the above code.

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

-

, '05/JAN/2008' as C0

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

karthik

SQLZ SSChampion Points: 12872 More actions · Answer 12

karthikeyan (4/4/2008)

SQLZ,

It is not working.

i got the following output when i execute the above code.

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

-

, '05/JAN/2008' as C0

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

Don't know what to say. If I run your create table statement and then run the T-SQL I posted (as-is) it works for me.



create table Dating

(

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'

Replace the exec statement with a print statement and see what is contained in @strSQL.

Karl
source control for SQL Server

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

[font="Verdana"]Not sure but you have to take help of Cursor, Dynamic SQL. Within the Cursor you have to build string for creating a Table and Select statement as well. i.e.

1. Write a SProc. Inside the SProc declare a cursor. Inside the cursor write a dynamic sql which will build Create Table statement, whose column count will be the row count of select statement, i.e. Select Date From Dating.

2. Simultaneously inside the same cursor, build one more string which will hold the actual data returned by the select statement.

3. Execute the create table statement so that it will create table.

4. Then again you need to write Insert statement dynamically and here you can concatenate the 2nd builded string then finally execute it. so that you can get the final output.

Hopes I tried well to show you the rough picture what can be done in such a situation. Try it out and let us know the problems, if any.

Thanks,

Mahesh

[/font]

MH-09-AM-8694

SQLZ SSChampion Points: 12872 More actions · Answer 14

Mahesh Bote (4/4/2008)

[font="Verdana"]Not sure but you have to take help of Cursor, Dynamic SQL. Within the Cursor you have to build string for creating a Table and Select statement as well. i.e.

1. Write a SProc. Inside the SProc declare a cursor. Inside the cursor write a dynamic sql which will build Create Table statement, whose column count will be the row count of select statement, i.e. Select Date From Dating.

2. Simultaneously inside the same cursor, build one more string which will hold the actual data returned by the select statement.

3. Execute the create table statement so that it will create table.

4. Then again you need to write Insert statement dynamically and here you can concatenate the 2nd builded string then finally execute it. so that you can get the final output.

Hopes I tried well to show you the rough picture what can be done in such a situation. Try it out and let us know the problems, if any.

Thanks,

Mahesh

[/font]

Mahesh, I don't think Kartikeyan has to resort to a cursor. The solution I provided (which was really your solution with a small modification) works for me, and it works for you, I have no doubt.

Not really sure on why it isn't working for Kartikeyan.

Karl
source control for SQL Server