|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Converting Rows into columnsI want to convert from rows to columns. I have a table like this. col0 Day Task ----- ----- ------- 1 Mon Office 1 Mon work 1 Mon Client 2 Tue Office 2 Tue Client 2 Tue Holiday 3 Wed Tour I want out put like this Mon Tue Wed ------ ----- ----- Office Office Tour work Client Client Holiday How to make a query to display like this. Thank you for your help. Ram. Here is one option:
How to rotate a table in SQL Server http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 And standby for a word from Steve... -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Ram" <uramanat***@hotmail.com> wrote in message news:uwvTa6A2GHA.476@TK2MSFTNGP06.phx.gbl... > Hai, > > I want to convert from rows to columns. > > I have a table like this. > > col0 Day Task > ----- ----- ------- > 1 Mon Office > 1 Mon work > 1 Mon Client > 2 Tue Office > 2 Tue Client > 2 Tue Holiday > 3 Wed Tour > > I want out put like this > > Mon Tue Wed > ------ ----- ----- > Office Office Tour > work Client > Client Holiday > > How to make a query to display like this. > > Thank you for your help. > > Ram. > AMie Rowland,
Thank you for your reply. I already seen that support. My Problem is different. In the same support query if you have one more row like this. 1995 1 25,000.00 then the out put will be YEAR Q1 Q2 Q3 Q4 ------------------------------------------------------------------- 1995 150,000.90 136,000.75 212,000.34 328,000.82 1996 328,000.82 422,000.13 728,000.35 0.00 But i want output like YEAR Q1 Q2 Q3 Q4 ------------------------------------------------------------------- 1995 125,000.90 136,000.75 212,000.34 328,000.82 1995 25,000.00 0.00 0.00 0.00 1996 328,000.82 422,000.13 728,000.35 0.00 How can we make. Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:%23sLr$VB2GHA.324@TK2MSFTNGP05.phx.gbl... > Here is one option: > > How to rotate a table in SQL Server > http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 > > > > And standby for a word from Steve... > > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Ram" <uramanat***@hotmail.com> wrote in message > news:uwvTa6A2GHA.476@TK2MSFTNGP06.phx.gbl... >> Hai, >> >> I want to convert from rows to columns. >> >> I have a table like this. >> >> col0 Day Task >> ----- ----- ------- >> 1 Mon Office >> 1 Mon work >> 1 Mon Client >> 2 Tue Office >> 2 Tue Client >> 2 Tue Holiday >> 3 Wed Tour >> >> I want out put like this >> >> Mon Tue Wed >> ------ ----- ----- >> Office Office Tour >> work Client >> Client Holiday >> >> How to make a query to display like this. >> >> Thank you for your help. >> >> Ram. >> > > Hello Ram,
If you have these rows: 1995 1 125,000.90 1995 2 136,000.75 1995 3 212,000.34 1995 4 328,000.82 1996 3 728,000.35 1996 2 422,000.13 1996 1 328,000.82 and you add this row: 1995 1 25,000.00 To do what want you have to be able to distinguish between the 2 rows of 1995 and quarter 1. Probably what you want is: year Q rank amount 1995 1 1 125,000.90 1995 1 2 25,000.00 Then you can have a row defined by year,Q,rank. Now Arnie is a superior sql guru and he can tell you the easiest way to do this. best, http://racster.blogspot.com Show quote "Ram" <uramanat***@hotmail.com> wrote in message news:ebq3ZYI2GHA.3372@TK2MSFTNGP04.phx.gbl... > AMie Rowland, > > Thank you for your reply. I already seen that support. My Problem is > different. > > In the same support query if you have one more row like this. > 1995 1 25,000.00 > then the out put will be > YEAR Q1 Q2 Q3 Q4 > ------------------------------------------------------------------- > > 1995 150,000.90 136,000.75 212,000.34 328,000.82 > 1996 328,000.82 422,000.13 728,000.35 0.00 > > But i want output like > YEAR Q1 Q2 Q3 Q4 > ------------------------------------------------------------------- > > 1995 125,000.90 136,000.75 212,000.34 328,000.82 > 1995 25,000.00 0.00 0.00 > 0.00 > 1996 328,000.82 422,000.13 728,000.35 0.00 > > How can we make. > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:%23sLr$VB2GHA.324@TK2MSFTNGP05.phx.gbl... > > Here is one option: > > > > How to rotate a table in SQL Server > > http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 > > > > > > > > And standby for a word from Steve... > > > > > > -- > > Arnie Rowland, Ph.D. > > Westwood Consulting, Inc > > > > Most good judgment comes from experience. > > Most experience comes from bad judgment. > > - Anonymous > > > > > > "Ram" <uramanat***@hotmail.com> wrote in message > > news:uwvTa6A2GHA.476@TK2MSFTNGP06.phx.gbl... > >> Hai, > >> > >> I want to convert from rows to columns. > >> > >> I have a table like this. > >> > >> col0 Day Task > >> ----- ----- ------- > >> 1 Mon Office > >> 1 Mon work > >> 1 Mon Client > >> 2 Tue Office > >> 2 Tue Client > >> 2 Tue Holiday > >> 3 Wed Tour > >> > >> I want out put like this > >> > >> Mon Tue Wed > >> ------ ----- ----- > >> Office Office Tour > >> work Client > >> Client Holiday > >> > >> How to make a query to display like this. > >> > >> Thank you for your help. > >> > >> Ram. > >> > > > > > > Steve Dassin,
Using rank i can distinguish. But i could get the output look like this. YEAR Q1 Q2 Q3 Q4 ------------------------------------------------------------------- 1995 125,000.90 136,000.75 212,000.34 328,000.82 1995 25,000.00 0.00 0.00 0.00 1996 328,000.82 422,000.13 728,000.35 0.00 Thanks for your answer. Show quote "Steve Dassin" <rac4sqlnospam@net> wrote in message news:OfDOLDJ2GHA.4976@TK2MSFTNGP02.phx.gbl... > Hello Ram, > > If you have these rows: > 1995 1 125,000.90 > 1995 2 136,000.75 > 1995 3 212,000.34 > 1995 4 328,000.82 > 1996 3 728,000.35 > 1996 2 422,000.13 > 1996 1 328,000.82 > and you add this row: > 1995 1 25,000.00 > > To do what want you have to be able to distinguish between the 2 rows of > 1995 and quarter 1. > Probably what you want is: > year Q rank amount > 1995 1 1 125,000.90 > 1995 1 2 25,000.00 > > Then you can have a row defined by year,Q,rank. > Now Arnie is a superior sql guru and he can tell you the easiest way to do > this. > > best, > http://racster.blogspot.com > > "Ram" <uramanat***@hotmail.com> wrote in message > news:ebq3ZYI2GHA.3372@TK2MSFTNGP04.phx.gbl... >> AMie Rowland, >> >> Thank you for your reply. I already seen that support. My Problem is >> different. >> >> In the same support query if you have one more row like this. >> 1995 1 25,000.00 >> then the out put will be >> YEAR Q1 Q2 Q3 Q4 >> ------------------------------------------------------------------- >> >> 1995 150,000.90 136,000.75 212,000.34 328,000.82 >> 1996 328,000.82 422,000.13 728,000.35 0.00 >> >> But i want output like >> YEAR Q1 Q2 Q3 Q4 >> ------------------------------------------------------------------- >> >> 1995 125,000.90 136,000.75 212,000.34 328,000.82 >> 1995 25,000.00 0.00 0.00 >> 0.00 >> 1996 328,000.82 422,000.13 728,000.35 0.00 >> >> How can we make. >> >> >> "Arnie Rowland" <ar***@1568.com> wrote in message >> news:%23sLr$VB2GHA.324@TK2MSFTNGP05.phx.gbl... >> > Here is one option: >> > >> > How to rotate a table in SQL Server >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 >> > >> > >> > >> > And standby for a word from Steve... >> > >> > >> > -- >> > Arnie Rowland, Ph.D. >> > Westwood Consulting, Inc >> > >> > Most good judgment comes from experience. >> > Most experience comes from bad judgment. >> > - Anonymous >> > >> > >> > "Ram" <uramanat***@hotmail.com> wrote in message >> > news:uwvTa6A2GHA.476@TK2MSFTNGP06.phx.gbl... >> >> Hai, >> >> >> >> I want to convert from rows to columns. >> >> >> >> I have a table like this. >> >> >> >> col0 Day Task >> >> ----- ----- ------- >> >> 1 Mon Office >> >> 1 Mon work >> >> 1 Mon Client >> >> 2 Tue Office >> >> 2 Tue Client >> >> 2 Tue Holiday >> >> 3 Wed Tour >> >> >> >> I want out put like this >> >> >> >> Mon Tue Wed >> >> ------ ----- ----- >> >> Office Office Tour >> >> work Client >> >> Client Holiday >> >> >> >> How to make a query to display like this. >> >> >> >> Thank you for your help. >> >> >> >> Ram. >> >> >> > >> > >> >> > >  |
|||||||||||||||||||||||
Other interesting topics