July 3, 2011 at 8:14 pm
Hello expert,
is it possible to create a grouping object based on column name in table?
I have for instance a table: Activities and 4 columns: Labor, Fabrication, Transport, Distribution.
I would like to create an object which will group those columns into Manufacturing und Distribution, e.g:
CASE WHEN Activities.ColumnName = 'Labor' THEN 'Manufacturing'
CASE WHEN Activities.ColumnName = 'Fabrication' THEN 'Manufacturing'
CASE WHEN Activities.ColumnName = 'Transport' THEN 'Logistik'
CASE WHEN Activities.ColumnName = 'Distribution' THEN 'Logistik'
END AS Activity
How could I do it?
Thanks
mtraore
July 5, 2011 at 5:06 am
If I understand the question correctly, you want to group on a column that has been defined with a case statement.
So you can do the following to add a group by :
[font="Courier New"]SELECT count(*),
CASE Activities.ColumnName
WHEN 'Labor' THEN 'Manufacturing'
WHEN 'Fabrication' THEN 'Manufacturing'
WHEN 'Transport' THEN 'Logistik'
WHEN 'Distribution' THEN 'Logistik'
END AS Activity
FROM Activities
GROUP BY CASE Activities.ColumnName
WHEN 'Labor' THEN 'Manufacturing'
WHEN 'Fabrication' THEN 'Manufacturing'
WHEN 'Transport' THEN 'Logistik'
WHEN 'Distribution' THEN 'Logistik'
END[/font]
July 5, 2011 at 7:32 am
Note sure how exactly your table structure & sample data looks like.Based on your description I have guessed the structure and expected output.
Declare @t table(Activities varchar(10),Labor varchar(10),Fabrication varchar(10),Transport varchar(10),Distribution varchar(10))
insert @t
select 'A','L','F','T','D'
Select * from @t
Select * from
(
select Activities,case columns
when 'Labor' then 'Manufacturing'
when 'Fabrication' then 'Manufacturing'
when 'Transport' then 'Logistik'
when 'Distribution' then 'Logistik'
end Activity from(
select * from @t)t
unpivot(col for columns in(Labor,Fabrication,Transport,Distribution))v
)T group by Activities,Activity
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 11, 2011 at 6:14 am
Hallo SSC-Enthusiastic,
It works.
Many thanks for you're help.
July 11, 2011 at 6:17 am
Hello SSCrazy,
sorry, I've forgenten to thank you for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply