Convert Rows to columns so...

Question

Post reply

Convert Rows to columns so...

fausto usme

SSC Enthusiast

Points: 125
More actions
December 2, 2008 at 7:36 pm

#125255

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

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

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krayknot SSCertifiable Points: 7998 More actions · Answer 3

fausto usme (12/2/2008)
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

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
kshitij@krayknot.com
www.krayknot.com

fausto usme SSC Enthusiast Points: 125 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 5

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

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fausto usme SSC Enthusiast Points: 125 More actions · Answer 6

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

Garadin One Orange Chip Points: 29613 More actions · Answer 7

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[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 8

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fausto usme SSC Enthusiast Points: 125 More actions · Answer 9

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..

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

Garadin One Orange Chip Points: 29613 More actions · Answer 10

Where are you getting the information on what the status of each room is? It's not in this query. Do you already have another way to get that information?

What about column names? With an indeterminate number of rooms, figuring out what to name the columns dynamically becomes an issue.

It seems like this should be able to be handled at the application level by your winform, which would allow this query to be not only extremely easy to write, but very efficient. You could always add a Floor number to your output to allow application level grouping by floors. Your output would then look something like this:

FloorRoomStatus

1101Clean

1102Available

2201Clean

2202Clean

2203Available

Seth Phelabaum

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

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

vicky-954852 SSC Enthusiast Points: 192 More actions · Answer 11

Hi!!

I hope this will help you

Create Table #tblRooms(idRoom int, vcRoom int, ID int null, ID2 int null)

Insert Into #tblRooms(idRoom , vcRoom)

select 1, 101

union all

select 1, 102

union all

select 1, 103

union all

select 1, 104

union all

select 1, 201

union all

select 1, 202

union all

select 1, 203

union all

select 1, 301

union all

select 1, 302

union all

select 1, 303

union all

select 1, 304

Update #tblRooms Set ID = convert(int, Substring(convert(nvarchar(3),vcRoom), 1,1)),

ID2 = convert(int,Substring(convert(nvarchar(3),vcRoom),2,2))

select * from #tblRooms

SELECT [1] AS Hab1, [2] AS Hab2, [3] AS Hab3, [4] AS Hab4 -- column

FROM (SELECT idRoom, vcRoom, ID, ID2 FROM #tblRooms) p -- information

PIVOT (MAX (vcRoom) -- cell

FOR ID2 in([1],[2],[3],[4])) AS pvt -- column

fausto usme SSC Enthusiast Points: 125 More actions · Answer 12

Hello Garadin,

here the answers

>>Where are you getting the information on what the status of each room is? It's not in this query. Do you already have another way to get that information?

yes, here is the query

SELECT tblRooms.vcRoom, tblStatesRoom.vcStateRoom, tblFloors.vcFloor

FROM tblStatesRoom INNER JOIN

tblRooms ON tblStatesRoom.inIdEstado = tblRooms.intIdEstado INNER JOIN

tblFloors ON tblRooms.intIdFloor = tblFloor.intIdFloor

and the results are:

101OccupiedFloor 1

102OccupiedFloor 1

103OccupiedFloor 1

104OccupiedFloor 1

105CleaningFloor 1

201OccupiedFloor 2

202OccupiedFloor 2

203ReservedFloor 2

204OccupiedFloor 2

205OccupiedFloor 2

301OccupiedFloor 3

302OccupiedFloor 3

303CleaningFloor 3

304OccupiedFloor 3

305Out of ServiceFloor 3

401AvailableFloor 4

402AvailableFloor 4

403OccupiedFloor 4

404OccupiedFloor 4

405OccupiedFloor 4

406OccupiedFloor 4

>>What about column names? With an indeterminate number of rooms, figuring out what to name the columns dynamically becomes an issue.

Exactly, the rooms in this hospital are about 20 per floor, but in others hospital could be more or

less.

So the columns must be generate dynamically always.

Now, i ask you, is more efficient for performance, do this in the winform or in Sql Server?

>>It seems like this should be able to be handled at the application level by your winform, which would allow this query to be not only extremely easy to write, but very efficient. You could always add a Floor number to your output to allow application level grouping by floors. Your output would then look something like this:

Floor Room Status

1 101 Clean

1 102 Available

2 201 Clean

2 202 Clean

2 203 Available

I'll prefer in database because the data come already. what do you thing about???

Garadin One Orange Chip Points: 29613 More actions · Answer 13

fausto usme (12/4/2008)
I'll prefer in database because the data come already. what do you thing about???

From a simplicity standpoint... it *might* be easier to have the database send it to you already pivoted... but that's mostly true because Jeff may write it for you ;). If you had to write it yourself, you'd likely have to learn just as much if not more about dynamic SQL and the PIVOT operator as you would about the C# methods to do this in your front end. I also say that it *might* be simpler, because I'm not familiar enough with the C# methods you may use to do this to know if not knowing the column names ahead of time will present as big a problem as pivoting the data.

From a performance standpoint, it's not even a question that it's way less efficient (at least for the database server) to attempt to pivot this information for you. You *may* save a bit of processing on your web server, but this shouldn't really be a primary concern. Performance bottlenecks are almost always at a database level, not a web server level, because it's very easy (and way cheaper in most cases, because you don't have to buy SQL licenses for a web server) to fix a web server performance issue by just throwing more hardware at it. Bad/inefficient query design takes a lot more time, knowledge and money to fix.

Seth Phelabaum

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

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

fausto usme SSC Enthusiast Points: 125 More actions · Answer 14

Thanks,

I thik that the better is do all in the front end, just to receive data from sql server and manipulate them in the winform in the grid view.

I apreciate to spend your time to explain to me everything.

Thanks a lot.

fausto