Grouping - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-01-26 : 04:50:52

hi

I have a table with product name only.
Rose
Lily
beef
fish
lamd
lavendar

i want to group the above product into 2 groups. Flower and Meat. How should i go about it? Thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-26 : 05:20:17

Create a table which stores the category/group info and add the primary key of this table into the one where Product info is stored, this will tell you the group of every product and to do the needful.

Cheers!
MIK

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-01-26 : 05:57:47

hi

As i am not allow to change the schemas and also there only 6 products so i came up with solution

Select 'Flower',Count(ProductName) from product
where ProductName in('Rose','Lily','lavendar')
union all
Select 'Meat',Count(ProductName) from product
where ProductName in('beef','fish','lamb')

So the result would be
Flower 3
Meat 3

But how do i turn them into single row which is Flower 3 Meat 3. Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-01-26 : 06:08:36

hi

I have found the solution

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-01-26 : 06:09:31

it should be

declare @test varchar(1000)

Select @test = 'Flower' + ' ' + Count(ProductName) from product
where ProductName in('Rose','Lily','lavendar')

Select @test = @test + ' Meat' + ' ' + Count(ProductName) from product
where ProductName in('beef','fish','lamb'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-26 : 21:44:12

quote:
Originally posted by sg2255551

it should be

declare @test varchar(1000)

Select @test = 'Flower' + ' ' + Count(ProductName) from product
where ProductName in('Rose','Lily','lavendar')

Select @test = @test + ' Meat' + ' ' + Count(ProductName) from product
where ProductName in('beef','fish','lamb'

you will need to convert the count result to string before concatenate with 'Flower'

KH
[spoiler]Time is always against us[/spoiler]

chanluongbus
Starting Member

5 Posts

Posted - 2011-01-27 : 11:35:24

Thanks for sharing the result. I am writing a blog for slq as well. It's on http://ssrsdeveloper.blogspot.com/p/sql101.html I will include this there. I think you can use a case statement on this as well right? case when product in ('1','2') then 1 as flower, case where product in ('3','4') then 1 as beef.

CL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources