Convert Rows to columns so...

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Convert Rows to columns so...

22 posts, Page 1 of 3

1 2 3 »»»

Convert Rows to columns so...

Rate Topic

Display Mode

Topic Options

Author

Message

fausto usme

Posted Tuesday, December 02, 2008 7:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 27, 2009 8:10 AM
Points: 8, Visits: 22

Hello People,

i need to do the next.

I have with follow data:

Table: Rooms

IdRoom
101
102
103
104
201
202
203
301
302
303
304
401
402
403

and it will be so:

101 102 103 104
201 202 203
301 302 303 304
401 402 403

is possible this?

Thanks in advance

fausto

Post #612520

Ken Simmons

Posted Tuesday, December 02, 2008 8:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, July 02, 2009 1:57 PM
Points: 1,081, Visits: 2,314

Something like this.

--Sample Data
Declare @Tbl Table (RoomID int)

Insert INTO @Tbl
Select 101
Union All
Select 102
Union All
Select 201
Union All
Select 202
Union All
Select 203

--Sample Query
SELECT
Distinct
RoomList = substring((SELECT ( ', ' + Cast(RoomID as varchar(50)))
FROM @tbl t2
WHERE Substring(Cast(t1.RoomID as varchar(50)),1,1) =
Substring(Cast(t2.RoomID as varchar(50)),1,1)
FOR XML PATH( '' ) )
, 3, 1000 )FROM @Tbl t1

Here is a link with more examples.
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList&referringTitle=Home

Ken Simmons
http://columbusga.sqlpass.org

Post #612531

Jeff Moden

Posted Tuesday, December 02, 2008 10:52 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 16,212, Visits: 8,848

fausto usme (12/2/2008)

Hello People,

i need to do the next.

Why? What's the goal or business rule other than someone said they wanted it?

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #612583

krayknot

Posted Tuesday, December 02, 2008 11:25 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 18, 2009 4:41 AM
Points: 321, Visits: 441

fausto usme (12/2/2008)

declare @colname varchar(100)
declare @rowcolname varchar(1000)
set @rowcolname = ''

declare col CURSOR FOR
select id from actionstate

open col
fetch next from col into @colname
while @@fetch_status = 0
begin
set @rowcolname = @rowcolname + '[' + @colname + '] int,'
fetch next from col into @colname
end
close col
deallocate col

print('Create TAble tablename('+ @rowcolname + ')')
exec('Create TAble tablename('+ @rowcolname + ')')

select * from tablename
drop tablename

kshitij kumar
Senior Software Engineer
Pune.

krayknot@yahoo.com

Post #612588

fausto usme

Posted Wednesday, December 03, 2008 6:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 27, 2009 8:10 AM
Points: 8, Visits: 22

Hello Jeff,

the business is about the rooms in a hospital,
so i need show a grid in a winform c# that would be
so:

this is the table

tblRooms
idRoom vcRoom
1 101
1 102
1 103
1 104
1 201
1 202
1 203
1 301
1 302
1 303
1 304

And the goal is can show so:

Rooms
101 102 103 104
201 201 203
301 302 304 304

is possible people

Thanks a lot.

fausto

Post #612773

Jeff Moden

Posted Wednesday, December 03, 2008 6:57 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 16,212, Visits: 8,848

Yes... everything is possible... but what are you going to do if there are 40 rooms on the same floor?

--Jeff Moden

Post #613319

fausto usme

Posted Thursday, December 04, 2008 6:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 27, 2009 8:10 AM
Points: 8, Visits: 22

Hello Jeff,

yes, you question is good, but, that´s ins´t important, because
the query must bring the same answer, just will bring some columns
more, well, i think so...

Jeff, by the way, the query must bring each data in each column, not all
of data in just one column, you understand me??.

greetings

Post #613619

Garadin

Posted Thursday, December 04, 2008 6:50 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, June 11, 2009 11:23 AM
Points: 766, Visits: 1,909

You're looking more along the lines of UNPIVOT instead of concatenation then. Since you have no idea what your maximum number of rooms can be, you're also looking at needing dynamic sql.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. ;)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots

Post #613634

Jeff Moden

Posted Thursday, December 04, 2008 6:57 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 16,212, Visits: 8,848

The question is still important because it will determine how the columns in the table are named and a couple of other things like whether to use A VARCHAR(8000) or a VARCHAR(MAX), etc. You haven't identified if the rooms can have letters in them and you haven't identified the data types for the final output never mind telling us what the column names should be. It would also be nice if you told us the business reason behind this so we can figure it out with you.

I'm on my way to work... please determine the answers to the above so I can help... I like to write code only once. ;)

--Jeff Moden

Post #613645

fausto usme

Posted Thursday, December 04, 2008 7:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 27, 2009 8:10 AM
Points: 8, Visits: 22

ok Jeff,

i here go:

the business basically is identify the state for each room into
an hospital.

Each state can be: cleaninig, available, occupied, reserved and out of service.

And i need show in a winform each room like a horizontal grid so:

101 102 103 104 etc etc etc....
201 202 203 204 etc etc etc....
etc..
etc..

Now for each room depending your state i paint each cell into grid
in one color, for example: green for room available, red for room
cleaning, etc etc....

So,is good for good display in the program that the nurses seen all
rooms with the number and the color behind.

Jeff, if isn´t clear, please, tell me.

And Thanks a lot.

fausto

Post #613690

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions