Single to Multiple columns

15 Sep 2006 1:32 PM

Rob T

I have a large table filled with product serial numbers. Similar to how a
datagrid in .net works, I would like to be able to query the single column,
but have it return the data in 3 columns. I have no problem having 3 unique
column names returned.

For example:

If the data was 1,2,3,4,5,6,7,8,9,10

I want returned:

Col1    Col2    Col3
1    2    3
4    5    6
7    8    9
10

Thanks.

15 Sep 2006 2:06 PM

Anith Sen

For some ideas, see:
http://www.projectdmx.com/tsql/sqlarrays.aspx

--
Anith

15 Sep 2006 2:24 PM

Rob T

Sorry, I misrepresented my data set. The data is not a single record with
comma delimiters. Each value is a unique record in the data base.

Show quoteHide quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:uxo0fAN2GHA.2176@TK2MSFTNGP04.phx.gbl...
> For some ideas, see:
> http://www.projectdmx.com/tsql/sqlarrays.aspx
>
> --
> Anith
>

15 Sep 2006 3:42 PM

Anith Sen

Then, you can do:

SELECT MAX( CASE WHEN seq % 3 = 0 THEN col END ) AS "col1",
       MAX( CASE WHEN seq % 3 = 1 THEN col END ) AS "col2",
       MAX( CASE WHEN seq % 3 = 2 THEN col END ) AS "col3"
FROM ( SELECT t1.col, ( COUNT(*) - 1 )
           FROM t t1, t t2
          WHERE t1.col >= t2.col
          GROUP BY t1.col ) D ( col, seq )
GROUP BY seq / 3 ;

--
Anith

15 Sep 2006 2:28 PM

Damien

Rob T wrote:
Show quoteHide quote

> I have a large table filled with product serial numbers. Similar to how a
> datagrid in .net works, I would like to be able to query the single column,
> but have it return the data in 3 columns. I have no problem having 3 unique
> column names returned.
>
> For example:
>
> If the data was 1,2,3,4,5,6,7,8,9,10
>
> I want returned:
>
> Col1    Col2    Col3
> 1    2    3
> 4    5    6
> 7    8    9
> 10
>
> Thanks.

select
t1.Col as Col1,
t2.Col as Col2,
t3.Col as Col3
from
unnamed_table t1
    left join
unnamed_table t2
    on
      t1.Col = t2.Col - 1
    left join
unnamed_table t3
    on
      t2.Col = t3.Col - 1
where
t1.Col % 3 = 1
order by
t1.Col

Damien

15 Sep 2006 2:50 PM

Rob T

Thanks! that works great!

Show quoteHide quote

"Damien" <Damien_The_Unbelie***@hotmail.com> wrote in message
news:1158330536.887814.222470@i42g2000cwa.googlegroups.com...
> Rob T wrote:
>> I have a large table filled with product serial numbers. Similar to how
>> a
>> datagrid in .net works, I would like to be able to query the single
>> column,
>> but have it return the data in 3 columns. I have no problem having 3
>> unique
>> column names returned.
>>
>> For example:
>>
>> If the data was 1,2,3,4,5,6,7,8,9,10
>>
>> I want returned:
>>
>> Col1    Col2    Col3
>> 1    2    3
>> 4    5    6
>> 7    8    9
>> 10
>>
>> Thanks.
>
> select
> t1.Col as Col1,
> t2.Col as Col2,
> t3.Col as Col3
> from
> unnamed_table t1
>    left join
> unnamed_table t2
>    on
>      t1.Col = t2.Col - 1
>    left join
> unnamed_table t3
>    on
>      t2.Col = t3.Col - 1
> where
> t1.Col % 3 = 1
> order by
> t1.Col
>
> Damien
>