how to transpose one colomn to many columns ?

Question

Post reply

how to transpose one colomn to many columns ?

pdiagne83

SSC Rookie

Points: 41
More actions
March 27, 2012 at 1:55 am

#258310

I have this table
custid visitid pagenum pagename
001 1 1 acceuil
001 1 2 produit
001 2 1 home
002 1 3 rayon
002 1 4 rayon
001 2 2 produit
003 1 1 home
I want to convert the above table in the following form:
custid visitid page1 page2 page3 page4
001 1 acceuil produit null null
001 2 home produit null null
002 1 null null rayon rayon
003 1 home null null null
and at end
custid visitid page
001 1 acceuil>produit
001 2 homme>produit
002 1 rayon>rayon
003 1 home
I look forward to your reply. THANKS

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

looks like a potential cause for cross tabs or pivots

take a look here http://www.sqlservercentral.com/articles/Crosstab/65048/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

While a PIVOT will probably get you to the intermediate result, I say skip the intermediate result if you can with this:

DECLARE @x TABLE

(custid CHAR(3), visitid INT, pagenum INT, pagename VARCHAR(20))

INSERT INTO @x

SELECT '001' AS custid, 1 As visitid, 1 as pagenum, 'acceuil' as pagename

UNION ALL SELECT '001', 1, 2, 'produit'

UNION ALL SELECT '001', 2, 1, 'home'

UNION ALL SELECT '002', 1, 3, 'rayon'

UNION ALL SELECT '002', 1, 4, 'rayon'

UNION ALL SELECT '001', 2, 2, 'produit'

UNION ALL SELECT '003', 1, 1, 'home'

SELECT custid, visitid,

REPLACE(STUFF( (SELECT ',' + pagename

FROM @x x2

WHERE x1.custid = x2.custid and x1.visitid = x2.visitid

ORDER BY x2.custid, x2.visitid, x2.pagenum

FOR XML PATH('')), 1, 1, ''), ',', '>')

FROM @x x1

GROUP BY custid, visitid

Make sure the ',' that gets replaced is not a character that can appear in your pagename.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

pdiagne83 SSC Rookie Points: 41 More actions · Answer 3

pdiagne83

SSC Rookie

Points: 41

March 27, 2012 at 2:46 am

#1464787

THANK you

Can I get the result with PIVOT ?

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Unfortunately I can't help you with that as I'm no PIVOT-master (merely a Paduan) and I don't have access to SQL Server 2008 at this moment.

Still want to know why you need the intermediate result though.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Thinknook SSCrazy Points: 2531 More actions · Answer 5

Yes but as dwain.c mentioned, it will get you the intermediate results which you can simply concatenate to produce the final results. (have the Pivot in a CTE and concatenate in your main SELECT statement.

After alot of trial in the past, I did find CROSS TAB to be more efficient than PIVOT/UNPIVOT (only by little tho, and depends on the already existing setup on the table), so my recommendation would be to do this as a CROSS TAB.

/>L

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].

I believe in Codd
... and Thinknook is my Chamber of Understanding

pdiagne83 SSC Rookie Points: 41 More actions · Answer 6

I don't need the intermediate result

so I just like to do that stape by stape

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Doesn't PIVOT only work for a fixed number of columns?

What if your visitor visits 250 pages? I think the solution I proposed will work for that case.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

pdiagne83 SSC Rookie Points: 41 More actions · Answer 8

Thanks a lot dwain.c id.naji

I can't use dwain.c's methode because, there is 100000 rows in my table and I can't have all item of "pagename", that's why I think that PIVOT will be better.

I excluded all visitors who are over 15 visits.

pdiagne83 SSC Rookie Points: 41 More actions · Answer 9

sorry

I excluded all visitors who are over 15 pages.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

Never look a gift horse in the mouth.

SELECT custid, visitid,

REPLACE(STUFF( (SELECT TOP 15 ',' + pagename

FROM @x x2

WHERE x1.custid = x2.custid and x1.visitid = x2.visitid

ORDER BY x2.custid, x2.visitid, x2.pagenum

FOR XML PATH('')), 1, 1, ''), ',', '>')

FROM @x x1

GROUP BY custid, visitid

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

pdiagne83 (3/27/2012)
THANK you
Can I get the result with PIVOT ?

Just double checking... you want the result in separate columns instead of concatenated into one column, correct?.

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