column in multiple rows to multiple columns in one row 
Author Message
 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
 column in multiple rows to multiple columns in one row



UPDATE yourTable

[WHERE <any conditions>

--
- Anith



Sat, 12 Feb 2005 00:35:12 GMT
 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
 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
 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
 
 [ 5 post ] 

 Relevant Pages 

1. column in multiple rows to multiple columns in one row

2. SELECT multiple rows back as one row with many columns

3. translating multiple rows into multiple columns

4. MIN across multiple columns and multiple rows.

5. multiple rows versus multiple columns

6. Converting 1:M rows into a single row with multiple columns

7. Column values from multiple rows into a single column

8. Combining multiple rows into one column

9. Concatenating multiple rows on one column

10. Pivoting one source row into multiple destination rows

11. One source row to multiple destination rows


 
Powered by phpBB® Forum Software