asked on

use columns to create types

I would like to have a column named: TypeGroup
that will contains acronyms like: FGC, CatCode, FTC, SGC, PGC

those acronyms (type groups) will be from columns:

ItemCategoryCode ItemFamilyGroupCode ItemFamilyTypeCode ItemSweetenerGroupCode ItemProductGroupCode
---------------- -------------------- -------------------- ---------------------- --------------------
BULK WHITE STANDARD SUGAR
DROP PEANUT COLOR OR FLAVOR SUGAR 4M
DROP PEANUT STANDARD SUGAR 1M
DROP PEANUT STANDARD SUGAR 4M

I know I have to use a CASE statement, but I am not sure how to accomplish what I need to that I end up with the TypeGroup column first.

Any help would be great.

Thank you for your help.

Sharath S

How do you come up with those acronyms. Explain in plain English.

metropia

ASKER

I just did this:

FGC = ItemFamilyGroupCode
CatCode = ItemCategoryCode
FTC = ItemFamilyTypeCode
SGC = ItemSweetenerGroupCode
PGC = ItemProductGroupCode

Is that good example?

englanddg

I'm a bit confused as to what you are trying to do...could you elaborate a bit more?

chaau

I think what you want is column alias. If for example your column is called ItemFamilyGroupCode but you want in the select statement to show FGC you need to use an alias, like this:

SELECT ItemFamilyGroupCode  AS FGC, 
ItemCategoryCode AS CatCode,
ItemFamilyTypeCode  AS FTC,
ItemSweetenerGroupCode AS SGC,
ItemProductGroupCode AS PGC 
FROM Table1

Open in new window

metropia

ASKER

Yes.

There are 5 columns returned:

ItemCategoryCode
ItemFamilyGroupCode
ItemFamilyTypeCode
ItemSweetenerGroupCode
ItemProductGroupCode

I just want to create a column of GroupType based off on those columns (I eventually want to pivot my results, not there yet) - I came up with "acronyms" for the columns it just follows the first initial of the column name.

In the end I need t

he TypeGroup column to look like this:

metropia

ASKER

perhaps a union all? i don't know :/

englanddg

I think @chaau has a good solution for you, if you are dumping it into Excel for manipulation. The column header would be the TypeGroup you are looking for.

Then you could manipulate it with VBA pretty easily to get you the format you are looking for.

However, if you want it to come straight out of SQL like that? Hrm...

Is that excel screenshot above the end result you are looking for? If so, I think it's doable...but I don't have it offhand. At least, not without adding columns to your table that autopopulate the "groupcode" or dumping it into a temp table or something...

Hrm...interesting question. Hopefully someone has the answer offhand... <grin>

metropia

ASKER

I am using t-sql stored procedure. These results will go into a variable table.

metropia

ASKER

any suggestions on how to do this using just t-sql would be awesome. thank you so much.

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial