Vertical to Horizontal

23 Sep 2005 12:00 PM

XXX

I have a table which stores rows for same ID vertically:

ID    Attribute
1        1
1        2
1        XX
......

I have to transform this to horizontal format:

ID    Col1    Col2    Col3....
1        1        2            XX

Millions of rows are involved, so update is not a feasible option. The
horizontal table will have around 20 columns.

Any help is appreciated.

Thanks.

23 Sep 2005 12:25 PM

Roji. P. Thomas

Have a look at

http://www.aspfaq.com/show.asp?id=2462

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

Show quote

"XXX" <s*@nomail.com> wrote in message
news:O6MSkZDwFHA.3764@TK2MSFTNGP09.phx.gbl...
>I have a table which stores rows for same ID vertically:
>
> ID    Attribute
> 1        1
> 1        2
> 1        XX
> .....
>
>
> I have to transform this to horizontal format:
>
> ID    Col1    Col2    Col3....
> 1        1        2            XX
>
> Millions of rows are involved, so update is not a feasible option. The
> horizontal table will have around 20 columns.
>
> Any help is appreciated.
>
> Thanks.
>

23 Sep 2005 12:25 PM

Jens

http://aspfaq.com/show.asp?id=2462

HTH, jens Suessmeyer.

23 Sep 2005 12:28 PM

David Portas

Your design doesn't make much sense to me. As posted it doesn't look
like the usual EAV design. What decides which attribute goes in which
column? What is the rationale for using the "vertical" table design
rather than the "horizontal" one?

Perhaps you could do:

SELECT id,
MAX(CASE WHEN attribute = 1 THEN 1 END),
MAX(CASE WHEN attribute = 2 THEN 2 END),
...
FROM your_table
GROUP BY id ;

--
David Portas
SQL Server MVP
--

23 Sep 2005 2:36 PM

JT

This is not a database design issue; you basically describing a pivot table
or cross-tab, which is something that can best be done at the presentation
tier:
http://office.microsoft.com/en-us/assistance/HA010346321033.aspx
http://www.microsoft.com/businesssolutions/excel_pivot_tables_collins.mspx
http://www.windowatch.com/2004/april/crystal10_4.html

Show quote

"XXX" <s*@nomail.com> wrote in message
news:O6MSkZDwFHA.3764@TK2MSFTNGP09.phx.gbl...
>I have a table which stores rows for same ID vertically:
>
> ID    Attribute
> 1        1
> 1        2
> 1        XX
> .....
>
>
> I have to transform this to horizontal format:
>
> ID    Col1    Col2    Col3....
> 1        1        2            XX
>
> Millions of rows are involved, so update is not a feasible option. The
> horizontal table will have around 20 columns.
>
> Any help is appreciated.
>
> Thanks.
>