Grouping result sets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping result sets

Author

Topic

kopfgeldjagar
Starting Member

14 Posts

Posted - 2009-09-26 : 10:50:21

I am wondering if it is possible make a result set group values in a column when the values for another column are identical. For example, i have a 2 column table, where the first column is the product (For instance, 12 volt power supply.) I have 12 volt power supplies at 8 stores... Can i create a result set that just has a single row for 12 volt power supplies listing the product in one column and store numbers in the second column, instead of 8 rows of power supplies (1 row per store). Sorry if this doesnt make perfect sense. I am just trying to make my results as short as possible, it has to be printed out and distributed to around 100 people every week.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-26 : 11:19:07

I have never used that so I cannot give any great advice but have a look in BOL for pivot/unpivot.
Available since version 2005.

No, you're never too old to Yak'n'Roll if you're too young to die.

kopfgeldjagar
Starting Member

14 Posts

Posted - 2009-09-26 : 12:16:12

Thanks for the thought, but pivot won't work. It wants to created a row of sums or averages, instead of a list.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 00:29:11

do you mean this?


SELECT t.product,LEFT(sl.StoreList,LEN(sl.StoreList)-1) AS StoreValues
FROM (SELECT DISTINCT product FROM YourTable) t
CROSS APPLY (SELECT CAST(storevalue as varchar(10)) + ','
             FROM YourTable
             WHERE product=t.product
             FOR XML PATH(''))sl(StoreList)

kopfgeldjagar
Starting Member

14 Posts

Posted - 2009-10-05 : 12:54:00

that may work, i will try it today.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 13:34:31

ok...let us know how you got on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources