Appending Rows to Columns

Appending Rows to Columns

Post by Nathaniel E. Bible » Sat, 14 Jul 2001 06:30:41



Hey everyone,

I'm running Win2k Adv Srv's with SQL 2000 SP1.  I was just wondering if
anyone knew of an SQL statement (or group of statements) to append what
would normally return in rows into a single column.  This one has had me
stumped for a while.

i.e.

SELECT Column1, Column2 FROM Table1
returns
Column1  Column2
---------  ----------
Text1       Version1
Text1       Version2
Text2       Version1
...

Would there be a way (of course using a different query) to have it return
Column1   Column2
---------   ----------
Text1        Version1, Version2
Text2        Version1

With or without seperators?

Thanks,
Nate

 
 
 

Appending Rows to Columns

Post by Nathaniel E. Bible » Wed, 18 Jul 2001 04:54:31


Many thanks to Darren Brinksneader from
microsoft.public.sqlserver.programming...

Here is a sample of how to list a book title and a delimited list of authors
of that title from the PUBS database on SQL Server.  You should be able to
adapt this code quite easily to fit your scenario...

USE PUBS
SET NOCOUNT ON
CREATE TABLE #MyTempTable (title_id CHAR(15), AuthorList VARCHAR(500))




title_id FROM titleauthor)


 BEGIN


RTRIM(au_fname) + '; '
   FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id

   ORDER BY authors.au_id



AND title_ID IN (SELECT title_id FROM titleauthor)
 END

SET NOCOUNT OFF

SELECT Title, AuthorList
FROM #MyTempTable INNER JOIN Titles ON #MyTempTable.title_id =
Titles.title_id
ORDER BY title

DROP TABLE #MyTempTable

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT



Quote:> Hey everyone,

> I'm running Win2k Adv Srv's with SQL 2000 SP1.  I was just wondering if
> anyone knew of an SQL statement (or group of statements) to append what
> would normally return in rows into a single column.  This one has had me
> stumped for a while.

> i.e.

> SELECT Column1, Column2 FROM Table1
> returns
> Column1  Column2
> ---------  ----------
> Text1       Version1
> Text1       Version2
> Text2       Version1
> ...

> Would there be a way (of course using a different query) to have it return
> Column1   Column2
> ---------   ----------
> Text1        Version1, Version2
> Text2        Version1

> With or without seperators?

> Thanks,
> Nate


 
 
 

1. 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)

2. Conversion question

3. Updating a row column of a table using values from another row column

4. Insert icrement with leading letter and zero

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

6. YOUR OPINION ABOUT THE BEST RAD SOFTWARE DEVELOPMENT TOOL

7. Precision is Invalid when Adding Column to table using adox (columns.append)

8. Sybase Central / NT problems? Anyone?

9. select case return rows in one row (how to group by three column)

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

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

12. how do I combine 24 rows(3 columns) into one row (

13. how to transform rows into columns group by column