|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
rows to column sql server 2000the query gives me the foll o/p as column1 column2 A 1 B 20 C 10 D 13 E 14 but acct i want the o/p as col1 col2 col3 col4 col5 col6 col6 col7 col8 col9 A 1 B 20 C 10 D 13 E 14 any kind of help would be appreciated thanks Hi,
There is no PIVOT function in SQL server 2000. So you may need to create a temp (#) table and do this manually by writing scripts. Where as in SQL Server 2005 you could use the PIVOT function to transpose the rows to columns. Thanks Hari SQL Server MVP <xavier.sha***@gmail.com> wrote in message Show quote news:1157826042.109968.136420@m79g2000cwm.googlegroups.com... > Hi all, > > the query gives me the foll o/p as > > column1 column2 > A 1 > B 20 > C 10 > D 13 > E 14 > > > but acct i want the o/p as > > col1 col2 col3 col4 col5 col6 col6 col7 col8 col9 > A 1 B 20 C 10 D 13 E 14 > > any kind of help would be appreciated > > > thanks > but how do i construct the query?
thanks Hari Prasad wrote: Show quote > Hi, > > There is no PIVOT function in SQL server 2000. So you may need to create a > temp (#) table and do this > manually by writing scripts. Where as in SQL Server 2005 you could use the > PIVOT function to transpose > the rows to columns. > > Thanks > Hari > SQL Server MVP > > > <xavier.sha***@gmail.com> wrote in message > news:1157826042.109968.136420@m79g2000cwm.googlegroups.com... > > Hi all, > > > > the query gives me the foll o/p as > > > > column1 column2 > > A 1 > > B 20 > > C 10 > > D 13 > > E 14 > > > > > > but acct i want the o/p as > > > > col1 col2 col3 col4 col5 col6 col6 col7 col8 col9 > > A 1 B 20 C 10 D 13 E 14 > > > > any kind of help would be appreciated > > > > > > thanks > > Hi,
Take a look into below URL for examples in usig PIVOT in SQL 2000 and SQL 2005. http://sqlserver2000.databases.aspfaq.com/how-do-i-create-a-cross-tab-or-pivot-query.html Thanks Hari SQL Server MVP <xavier.sha***@gmail.com> wrote in message Show quote news:1157826935.550767.246390@m73g2000cwd.googlegroups.com... > > but how do i construct the query? > thanks > > Hari Prasad wrote: >> Hi, >> >> There is no PIVOT function in SQL server 2000. So you may need to create >> a >> temp (#) table and do this >> manually by writing scripts. Where as in SQL Server 2005 you could use >> the >> PIVOT function to transpose >> the rows to columns. >> >> Thanks >> Hari >> SQL Server MVP >> >> >> <xavier.sha***@gmail.com> wrote in message >> news:1157826042.109968.136420@m79g2000cwm.googlegroups.com... >> > Hi all, >> > >> > the query gives me the foll o/p as >> > >> > column1 column2 >> > A 1 >> > B 20 >> > C 10 >> > D 13 >> > E 14 >> > >> > >> > but acct i want the o/p as >> > >> > col1 col2 col3 col4 col5 col6 col6 col7 col8 col9 >> > A 1 B 20 C 10 D 13 E 14 >> > >> > any kind of help would be appreciated >> > >> > >> > thanks >> > > You could learn a little about RAC to make problems like
easier. create table ##ttt (column1 char(1),column2 int) insert ##ttt values('A',1) insert ##ttt values('B',20) insert ##ttt values('C',10) insert ##ttt values('D',13) insert ##ttt values('E',14) Exec Rac @transform='Max(column1) as X & Max(column2) as Y', @rows='dummyrow', @pvtcol='column1', @from='##ttt cross join (select 1 as dummyrow) x',@rank='', @row_totals='n',@grand_totals='n',@racheck='y', @rowbreak='n',@rotate='nest', @select='select _pvtcols_ from rac' X_1 Y_1 X_2 Y_2 X_3 Y_3 X_4 Y_4 X_5 Y_5 ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- A 1 B 20 C 10 D 13 E 14 Visit Rac @ www.rac4sql.net best, http://racster.blogspot.com  |
|||||||||||||||||||||||
Other interesting topics