column in multiple rows to multiple columns in one row
| Author |
Message |
|
Eric Pearso
#1 / 5
|
 column in multiple rows to multiple columns in one row I need to report data that exists in multiple rows as multiple columns. my data exists as (name and otherdata are the same)
(name, otherdata, itemid)
(name, otherdata, itemid)
(name, otherdata, itemid)
(name, otherdata, itemid)
(name, otherdata, itemid)
but I need to report it in one row as
(name, otherdata, itemid1, itemid2, itemid3, itemid4, itemid5)
How can this be done? (in a SQL stored procedure to return a result set
formatted that way)
|
| Sat, 12 Feb 2005 00:24:24 GMT |
|
 |
|
Anith Se
#2 / 5
|
column in multiple rows to multiple columns in one row
UPDATE yourTable
[WHERE <any conditions>
--
- Anith
|
| Sat, 12 Feb 2005 00:35:12 GMT |
|
|
|
Marin
#3 / 5
|
column in multiple rows to multiple columns in one row Will there always be 5 items? Or could there be a variable number? If the number was the same, you could just do a join on the same table several times to get all those values.
Quote: > I need to report data that exists in multiple rows as multiple columns. > my data exists as (name and otherdata are the same)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> but I need to report it in one row as
> (name, otherdata, itemid1, itemid2, itemid3, itemid4, itemid5)
> How can this be done? (in a SQL stored procedure to return a result set
> formatted that way)
|
| Sat, 12 Feb 2005 00:36:29 GMT |
|
|
|
Eric Pearso
#4 / 5
|
column in multiple rows to multiple columns in one row it's a variable amount, max right now is 5, may grow.
Quote: > Will there always be 5 items? Or could there be a variable number? > If the number was the same, you could just do a join on the same table > several times to get all those values.
> > I need to report data that exists in multiple rows as multiple columns.
> > my data exists as (name and otherdata are the same)
> > (name, otherdata, itemid)
> > (name, otherdata, itemid)
> > (name, otherdata, itemid)
> > (name, otherdata, itemid)
> > (name, otherdata, itemid)
> > but I need to report it in one row as
> > (name, otherdata, itemid1, itemid2, itemid3, itemid4, itemid5)
> > How can this be done? (in a SQL stored procedure to return a result set
> > formatted that way)
|
| Sat, 12 Feb 2005 00:45:26 GMT |
|
|
|
BAM Enterprise
#5 / 5
|
column in multiple rows to multiple columns in one row Use a pivot table. Pivot on the itemidX field --
William Main
Senior Programmer
CEI Technology inc.
A division of Motivational Multimedia Technologies Inc.
-------------------------------------------------------
NetMeeting callto:cei-tech.dns2go.com
-------------------------------------------------------
Quote: > I need to report data that exists in multiple rows as multiple columns. > my data exists as (name and otherdata are the same)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> (name, otherdata, itemid)
> but I need to report it in one row as
> (name, otherdata, itemid1, itemid2, itemid3, itemid4, itemid5)
> How can this be done? (in a SQL stored procedure to return a result set
> formatted that way)
|
| Sat, 12 Feb 2005 06:32:00 GMT |
|
| |
|
