beginner row to column ?

Question

Post reply

beginner row to column ?

christophe.bernard 47659

Ten Centuries

Points: 1169
More actions
October 27, 2015 at 3:52 am

#399232

Hi all,
i'm a beginner and i work with sql server 2008 i have a table as this :
declare @table table(id int, person varchar(100), actorType int)
INSERT INTO @table values(1, 'toto, toto', 1), (2, 'test, test', 2), (3, 'xxxxxx', 3), (4, 'me, me', 4)
, (5, 'ddsdsdsdsd', 2)
SELECT * FROM @table
and i wonder if it's possible to list row not on row but on column ..
if fact, i would like this result :
toto, toto 1 test, test 2 xxxxxxxx 3 me me 4
ddsdsdsdsd 2
......
as you can see in my temporary table i have several rows for the actor type = 2
How i can transform my table row in columns in fact ..
thanks for your time and your knowledge ..
christophe

Viewing 8 posts - 1 through 7 (of 7 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You can use cross tabs to convert rows into columns. You can fine more information in these articles:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

Here's an example based on your sample data.

SELECT MAX( CASE WHEN id = 1 THEN person END) AS person1,

MAX( CASE WHEN id = 1 THEN actorType END) AS actorType1,

MAX( CASE WHEN id = 2 THEN person END) AS person2,

MAX( CASE WHEN id = 2 THEN actorType END) AS actorType2,

MAX( CASE WHEN id = 3 THEN person END) AS person3,

MAX( CASE WHEN id = 3 THEN actorType END) AS actorType3,

MAX( CASE WHEN id = 4 THEN person END) AS person4,

MAX( CASE WHEN id = 4 THEN actorType END) AS actorType4,

MAX( CASE WHEN id = 5 THEN person END) AS person5,

MAX( CASE WHEN id = 5 THEN actorType END) AS actorType5

FROM @table

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Y.B. SSChampion Points: 11649 More actions · Answer 2

Your question isn't very clear. I'll take a stab at it but I can't be sure its what you are looking for. At the very least maybe I can push you in the right direction.

You can easily convert rows into columns using PIVOT. However, the pivot operation requires an aggregate in order to pivot on that column. In your case you have to cheat a bit in order to use an aggregate function therefore I created a RowNum column.

Here are some examples on how I used PIVOT on your data. Again, it may not be what you are looking for but its at least an example of how to use a PIVOT. If you can be more helpful with your question you might get the answer you are looking for.

declare @table table(id int, person varchar(100), actorType int)

INSERT INTO @table values(1, 'toto, toto', 1), (2, 'test, test', 2), (3, 'xxxxxx', 3), (4, 'me, me', 4)

, (5, 'ddsdsdsdsd', 2)

SELECT actorType, [1] AS Col1, [2] AS Col2

FROM(

SELECT actorType, person, ROW_NUMBER() OVER (PARTITION BY actorType ORDER BY actorType) AS RowNum FROM @table

) x

PIVOT (MAX(person) FOR RowNum IN ([1], [2])) AS pvt

SELECT RowNum, [1] AS ActorType1, [2] AS ActorType2, [3] AS ActorType3, [4] AS ActorType4

FROM(

SELECT actorType, person, ROW_NUMBER() OVER (PARTITION BY actorType ORDER BY actorType) AS RowNum FROM @table

) x

PIVOT (MAX(person) FOR actorType IN ([1], [2], [3], [4])) AS pvt

SELECT quote FROM brain WHERE original = 1
0 rows returned

christophe.bernard 47659 Ten Centuries Points: 1169 More actions · Answer 3

Hi yb751, Luis Cazares

thanks for your time that's exactly what I want ..

I don't know the function pivot but I can see that's very powerfull ..

@yb751 => your second query was exactly REALLY the goal 🙂

have a nice day

christophe

Y.B. SSChampion Points: 11649 More actions · Answer 4

christophe.bernard 47659 (10/28/2015)
Hi yb751, Luis Cazares
thanks for your time that's exactly what I want ..
I don't know the function pivot but I can see that's very powerfull ..
@yb751 => your second query was exactly REALLY the goal 🙂
have a nice day
christophe

Your welcome, just remember that the code has fixed columns, be mindful of your data possibly having more than 5 actor types. It is possible to use Dynamic SQL with PIVOT to get around that issue but it's a little tricky but worth checking out.

SELECT quote FROM brain WHERE original = 1
0 rows returned

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

christophe.bernard 47659 (10/28/2015)
Hi yb751, Luis Cazares
thanks for your time that's exactly what I want ..
I don't know the function pivot but I can see that's very powerfull ..
@yb751 => your second query was exactly REALLY the goal 🙂
have a nice day
christophe

You'll find that a pre-aggregated CROSS TAB is faster and uses fewer resources. See the comparison in the end of the first article that Luis pointed you to.

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

christophe.bernard 47659 Ten Centuries Points: 1169 More actions · Answer 6

Hello all,

thanks for all, I'm repeat perhaps 🙂 but that wonderfull to be find some help with this site ..